Sum Field A depending on distinct Field B

[#1]

Hi All,

I have a Problem with a bad performing application and hope for your good ideas fixing it.

It contains a large facttable and few Dimension tables in the data model. The frontend has different Sheets and one sheet with large performance issues (time until result, CPU and RAM consumption) containing one Pivot table with up to 10 dynamic dimensions + 2 expressions.

The values that result from one Expression should only be calculated for FieldA (Value), if FieldB (Unit) is distinct for the combination of fields that is currently shown in the Pivot table. This is to avoid summing up values that have different Units (in original, it could be '€ Currency' and 'Pieces'). One of the dimensions in the Pivot could be Unit, but does not have to be Unit.

Therefore, a colleague implemented an Expression if(count(distinct Unit)=1, sum({<Flag={1}>} Value))

This is not performing very well, as it has to perform the if(Count(distinct Unit)) with every line.

As this description could be quite confusing, I added an example script

The first idea of putting the logic into the script in any way was already withdrawn, as the dimensions of the Pivot are too dynamic.

Anyone of you having a good idea how to implement this in a better way?

[a_mullick]

Hi,

I haven't tested it fully, but see if it works...

I created a key based on the data you gave me: simply "Order+Position" so we get this:

sum({<

Key={"=aggr(count({<Flag={1}>} DISTINCT Unit), Order, Position)=1"},

Flag={1}>} Value)

Basically, this does the sum over Value, for rows where the count of distinct Units over Order and Position is 1.

Thanks,

azam

[a_mullick]

Attched is your test; modified with my "solution"....(I hope!)

[#4]

Hi Azam,

the idea of the solution is really good.

However, there is a problem as it should show all values when you add "Unit" as dimension.

The original table is a Pivot with dynamic dimensions and has "Unit" as an optional dimension.

So it should show the result that is also produced by your suggestion, if field "Unit" is not selected - and show the list as shown in the green example when field "Unit" is added.

The solution you provided is missing  the lines 400 / 2 and  500 / 1 when the field "Unit" is added.

[a_mullick]

Hi,

Oh... I see. Is it acceptable to have 2 expressions? One for the case where Unit is a dimension and one for the case where it is not?

Thanks,

Azam

[a_mullick]

Hi,

Here's a version with dynamic reporting, which I think is what you said you're doing. I'm not sure how you implement your dymanic reporting, but I'm assuming it's along these lines:

Customizable Straight Table

So this solution uses two expressions to calucalte sum over Value, but the one used is based on whether the user has choosen to add Unit as a dimension. Hopefully it is still faster than using an 'If(count...' per row.

Thanks,

Azam