1

# 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

## 2replies

• Jason_Picker
• 3 yrs ago
• Reported - view

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:

• Erik
• 3 yrs ago
• Reported - view

Not exactly what I was looking for but Jason's answer here helps.