How to calculate aggregate correctly without using underlying details
Hello,
Background: We use Pyramid 2020.15.046 against a SSAS datasource.
I want to calculate the amount of interesting clients per location without using clients in the table, can I make a formula in Pyramid or is this something which needs to be built in the datasource?
Raw data:
| Location | Client | Client lives at the location? | Client has work at the location? |
| London | Dave | Yes | Yes |
| London | Jerry | No | Yes |
| London | Sue | Yes | No |
| London | Ellen | No | No |
Table that I want to create from this source:
| Location | How many clients live here? | How many clients work here? | How many clients live here without working here? |
| London | 2 | 2 | 1 |
Using a formula like:
How many clients live here without working here= IIf(DataPoint(Lives="Yes";Measure=# clients)-DataPoint(Works="Yes";Measure=# clients)<=0,NULL,DataPoint(Lives="Yes";Measure=# clients)-DataPoint(Works="Yes";Measure=# clients))
I am only able to create the following solution:
| Location | Client | How many clients live here without working here? |
| London | Dave | 0 |
| London | Jerry | 0 |
| London | Sue | 1 |
| London | Ellen | 0 |
2 replies
-
Erik,
There are two ways you can solve this on the client-side (i.e. via Pyramid Formulate) depending on what is currently available in the Cube you are using. First approach assumes you already have a measure in the cube defined as a Distinct Count measure for the # clients. You can then just create the 3 measures you are looking for by using simple Data Point objects.
How many clients live here? is calculated by selecting the # clients measure and the Lives value of Yes. If you were writing this formula in Script mode, it would look like this:
([Measures].[# clients], [Lives].&[Yes])
If you were to write this using the UI, it would look similar to my example below:
You would then repeat the process for the How many clients work here? formula.
([Measures].[# clients], [Works].&[Yes])
You would then create a third formula that would use the other two formulas based on the logic you outlined.
IIF( ( ([Measures].[# clients], [Lives].&[Yes]) - ([Measures].[# clients], [Works].&[Yes]) ) <= 0, NULL, ([Measures].[# clients], [Lives].&[Yes]) - ([Measures].[# clients], [Works].&[Yes]) )
Here is an example from the UI:
These Data Point objects automatically aggregate so to use them in a report, you just add the elements you need, such as Location in your example.
If your cube does not already have a Distinct Count measure, you can create one using the Customer attribute and the Count function. The challenge with creating these types of measures client-side is that in order for the count to fully include the query context, you have to add a couple other functions, such as NONEMPTY and EXISTING. So putting it all together, it would look like this:
COUNT( NONEMPTY( EXISTING [<my dimension>].[Client].[Client].Members, [Measures].[<my measure>]))
Without knowing your exact model object names, I put in placeholders for the dimension and measure. Hopefully that makes sense. Here is an example from my cube:
Here is an example of how it would look compared to the Distinct Count measure I already have in my cube. The count on the left is my client-side formula and the count on the right is the distinct count in the cube:
-
Not exactly what I was looking for but Jason's answer here helps.