Aggregating clients for a location while taking into account other locations
Good morning,
background: we use Pyramid 2020.15.046 against a SSAS datasource, tabular model.
I have a user wish, which I don't know how to build.
The user wants to know for a certain location how many clients live there, but do not work at all (not even at a different location). In the example below (raw data), the user wants the end result to look like this:
Amsterdam: 1 client lives there, while not working at all.
So he doesn't want to include Sue, because she does work (albeit at a different location), he only wants to include Ellen. The user is not allowed to see client names because of privacy concerns, so we have to aggregate the results to location level. I only manage my end result to count both Sue and Ellen (while I only want Ellen to be counted, and Sue to be excluded).
Can I make a formulation in Pyramid for this, or is this something which needs to be built in the datasource/datawarehouse?
Example, raw data:
Location client Living location Working location
Amsterdam Dave Yes Yes
Amsterdam Sue Yes No, but works elsewhere
Amsterdam Ellen Yes No, doesn’t work
4 replies
-
@Maaike van den Berg, Erik
-
Assuming your data does clearly distinguish between the two NO values, then you can create the metric in the Formulate module via a simple Data Point object. In order to demonstrate this, I created a sample dataset based on your criteria and my model contains a measure called Count, which is just a count of rows:
I then created a formula that returns the count for clients that live in a location but do not work:
Finally, I created a report that shows the proper counts without exposing the clients.
If you want the result to look exactly like your sample above, you can use the Dynamic Text object in Illustrate to take the results and display them as you wish.
Hope this helps.
-
Jason Picker Hi Jason, thank you for your answer! I'm afraid our data does not clearly distinguish between the two 'no'-values, I didn't put that clearly enough in my description. My apologies.
The data looks like the data below: each agreement (working/living) has a separate line in the data:
Location client Product Product category
Amsterdam Dave A Living
Amsterdam Dave B WorkingAmsterdam Sue C Living
Utrecht Sue D Working
Amsterdam Ellen A Living
So you see that Dave has only 1 location for both living and working, Sue has 2 locations, and Ellen only has a product in the category 'living', and no product in the category 'working'. When I select Amsterdam, I only want to Ellen to be counted as 'living without working'.
Thank you again in advance!
-
Janneke Richter The quick answer is YES, you can still get what you want. There are a number of ways to solve the problem. I am including one example. I am making a bunch of assumptions about the rest of your model so this solution may not work exactly as is for you.
I'm going to start with filtering the list of clients where there is a record for Living in the specified location. I will then just count those that only have a single record (i.e. Living but no Working record). You could use a mix of UI and code (MDX) but to keep it simple, I am going to use raw MDX via the Script Mode:
SUM( NonEmpty([My Table].[Client].MEMBERS, {[Product Category].&[Living]}) , IIF( ([My Table].[Client].CurrentMember, [Product Category].[All], [Measures].[Row Count]) = 1, 1, 0) )
Hopefully that helps you create what you need.
-