Aggregating at Dimension level which is not present in Staright Table.

[rajkumart]

Hi Dear,

I wanted to aggregate the measure at the dimension which is not present in the table.

EX:

In the straight Table, I have Cust NAme and Cust ID and Measure is the sum(Sales) I want to Aggregate the values at Net Due date Dimension level.

[sunny_talwar]

May be use Aggr() function.... but with just Sum(Sales) as expression... what difference will it make it you aggregate at additional dimensions?

[rajkumart]

Thank you for the response Sunny,

Just as a reference i have written as Sum(Sales). Actually its there set analysis.

[rajkumart]

Expression looks like this

if((Date([Net Due Date]))-[Report Creation date]>=-15,

if((Date([Net Due Date]))-[Report Creation date]<0,

sum({<[G/L Account]={'0000121240','0000121108'},[Clearing Date]={'NoClearingDate'}>}[Amount - GC]),'FALSE'

),'FALSE')

Below is the screenshot for the Tabel and i wanted to aggregate at Net Due date level.

[sunny_talwar]

May be do this

Aggr(

if((Date([Net Due Date]))-[Report Creation date]>=-15,

if((Date([Net Due Date]))-[Report Creation date]<0,

sum({<[G/L Account]={'0000121240','0000121108'},[Clearing Date]={'NoClearingDate'}>}[Amount - GC]),'FALSE'

),'FALSE')

,Dimension1, Dimension2, Dimension3, ...)

[rajkumart]

I have tried the same before I post this discussion. But i dint get

[rajkumart · Accepted solution]

This way I got a solution

sum({<[G/L Account]={'0000121240','0000121108'},[Clearing Date]={'NoClearingDate'}>}

if(date([Net Due Date])-[Report Creation date]>=-15 and date([Net Due Date])-[Report Creation date]<0,

[Amount - GC]))