Calculate Quota expression by Fact values in Pivot Table

[lylererger]

Hi frends !

I faced the next problem. I need to show Quota on by Fact values in Pivot Table on the all levels of aggregations. How I can do that?

On the screenshot below chart "1" Quota and Fact expressions, but if we hide Quota expression, in Fact there will be no zeros.

In chart "2" - look's like result what we need, BUT it works on the 4 and 3 level of Dimentionality(), because we wrote, the next expression:

If("Fact">0,
  Max( TOTAL<Collection, Category, Group, Subgroup>
  Aggr(
  Sum (Quota)
  , Collection, Category, Group, Subgroup)
  )
  )

and it dont works when we looking at "Quota" on Category and Collection level.

Error looks like here:

Because expression we wrote to Quota stops working when the Fact > 0, and when we drill down to the Collection and Category, Fact still > 0.

So hope that clearly explaned the current problem.

May be we need to use the calculated dimention, but how i'll be aggregating Quota expression by calculated dimention?

Please help! Help me to find the right way to write expression for Quota.

Any advices, solutions?

Final result shoul look like here:

and

Another words we need to say to the Quota expression: Look at the last granular level, find only Fact > 0 and show Quota only by those fields (values of fields), where Fact > 0.

P.S.: Demo source in attachment.

Thanks in advance.

[sunny_talwar]

Try this expression:

Max(TOTAL <Collection, Category, Group, Subgroup> Aggr(If(Sum(Fact) > 0, Sum(Quota)), Collection, Category, Group, Subgroup))

[sunny_talwar]

Oh so you still want to see the 0's when you have the Group and Subgroup Expanded???

Is that the goal?

[sunny_talwar]

What if you use Min instead of Max?

Min(TOTAL <Collection, Category, Group, Subgroup> Aggr(Sum(Quota), Collection, Category, Group, Subgroup))

[#5 · Accepted solution]

IF "Sum(Fact)" have big Formula with "AGGR" and other functions, method by sunindia don`t work.

This method will work with big formulas.

1) we create "Variable" For string with only true Subgroup(last DIM in AGGR functions)

vTest = CHR(39)&CONCAT(IF( Aggr(Sum (Fact) , Collection, Category, Group, Subgroup) >0, Subgroup),CHR(39)&','&CHR(39))&CHR(39)

2) Add to "set analysis" in "Sum (Quota)". => Sum ({<Subgroup={$(vTest)}>} Quota)

Now in set analysis, we will take only Subgroups where "Sum (Fact)>0".

[lylererger]

I'm using complex formula for Fact with Aggr func, then aggr(sum(aggr(sum...doesn't works...

But the final solution - is to use peace of SunIndia logic "If(Sum(Fact) > 0" and Max logic with variable "CHR(39)&CONCAT(IF( Aggr(Sum (Fact) , Collection, Category, Group, Subgroup) >0, Subgroup),CHR(39)&','&CHR(39))&CHR(39)" to.

Thanks a lot for yous help.