Small correction help needed in the expression

[mahitham]

Hi Experts,

I have used below expression in the table for calculating the percentage.

The manual calculation for the percentage is

Each Report Date wise each name wise individual Scope Sum(CC1)/Each Report Date wise each name wise Total Sum(CC1)

By the below expression I am getting the Total at Name level. Please help me to get the Total at Each Report date wise Individual Name Total.

Please find the below sample app.

Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),
Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,
[Reporting Date],Date, Name, SCOPE))

/

Sum(TOTAL <Name> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +
RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),
Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),
Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE))

[sunny_talwar]

What do you want to get? It seems you want 69% and 31% and you are getting those numbers?

[mahitham]

Hi stalwar1,

After selecting Reporting Date and Name=aim1 I am getting the correct percentage.

But by default need to show the percentage in the report.

Please help me on this.

Thanks in advance.

[mahitham]

In the app there is number of dates. Other Dates also contains the same Name as aim 1 in the Percentage need to divide only 18.08.2018 report date aim1 total.

[sunny_talwar · Accepted solution]

This

Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),

Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,

[Reporting Date],Date, Name, SCOPE))

/

Sum(TOTAL <Name, [Reporting Date]> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +

RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),

Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),

Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE, [Reporting Date]))

[mahitham]

Hi stalwar1,

Thanks for your reply. The percentage is getting correct. Here I am getting one more issue.

I have to multiple this percentage with 18.08.2018 > aim1> Individual Scope Amounts.

Here getting wrong amounts.

I have used below expression. Could you please help me on this.

=Sum(Amount)
*

(Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),
Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,
[Reporting Date],Date, Name, SCOPE))
/
Sum(TOTAL <Name, [Reporting Date]> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +
RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),
Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),
Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE, [Reporting Date]))
)

[sunny_talwar]

I have no clue what you mean? We are not even using Amount, what is that expression for? Is this another issue?

[mahitham]

Hi stalwar1,

I have one more requirement in the same expression.

I have applied default selection extension  in the app with Max([Reporting Date])

So  I am getting only 19.08.2018 data in the table by default.

Could you please help me to add [Reporting Date]= in the below expression for avoiding default Max([Reporting Date]) selection from extension.

=Sum(Amount)

*

(Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),
Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1,
[Reporting Date],Date, Name, SCOPE))

/

Sum(TOTAL <Name, [Reporting Date]> {<SCOPE>} Aggr(RangeMax(0,Sum({<SCOPE>}Value)) +
RangeMax(Sum({<SCOPE>}Item1),Sum({<SCOPE>}Item2),Sum({<SCOPE>}Item3),Sum({<SCOPE>}Item4),
Sum({<SCOPE>}Item5),Sum({<SCOPE>}Item6),Sum({<SCOPE>}Item7),Sum({<SCOPE>}Item8),Sum({<SCOPE>}Item9),
Sum({<SCOPE>}Item10),Sum({<SCOPE>}Item11))/5+0.1, Date, Name, SCOPE, [Reporting Date]))
)

Thanks in advance.