3 Replies Latest reply: Aug 2, 2018 7:08 AM by Sunny Talwar

# Complex Conditianal Sum with Max ,Agg  row fields

Hello Everyone,

I'm Trying  to Fetch a compleat Row From Data

| CA  | Month_ca  | Year_ca |

52,5        6              2018

44,4        7              2018

25,6        4              2017

....

I was able to do This to fetch the hole row data

Record fetchin with Month and year.

Expression  =       Max(aggr(CA_Month,Year_CA,Month_CA))

Label =FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) )

& ' - '&

FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) )

The Result is A Kpi Indicator that shows  :

2018 - 7

52.5

How ever I need to retrive another Data  basicly a Sum of Sales for the same Year_CA, and Month_CA

So This is what i did  and it Does not Work

Expression =

Max(aggr(Max(CA_Month),Month_CA,Year_CA)) -

Sum({

<

[Date.autoCalendar.Year]  = {\${FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) )}} ,

[Date.autoCalendar.Month] = {\${FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) )}} ,

[ODS_GLOBAL.Type de pièce] ={'Avoir'}

>

} [Total HT])

Thank you verry much.

• ###### Re: Complex Conditianal Sum with Max ,Agg  row fields

May be this

Date(Max(Date#(Year_ca * 100 + Month_ca, 'YYYYMM')), 'YYYY - MM') & Chr(10) &

FirstSortedValue(Aggr(Sum(Value), Year_ca, Month_ca), -Aggr(Date#(Year_ca * 100 + Month_ca, 'YYYYMM'), Year_ca, Month_ca))

• ###### Re: Complex Conditianal Sum with Max ,Agg  row fields

by the way i already found a solution after some hourse of documentation reading .

i have a variable  MonthlyRecord , i substract a value "ReturnedCash" wich i Sum from another table with these conditions
Month and Year corresponding to Month and Year of the MonthlyRecord.
So the first expression gets the converted month to a Date and the second gets the Year , put the hole thing in a sum and it's done !

Hopes it easy to read !!!

this is how i did for the conditions :

<

[Date.autoCalendar.Month] ={'\$(=Month(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

[Date.autoCalendar.Year] = {'\$(=Year(Makedate((Num#(FirstSortedValue( aggr(Year_CA,Month_CA,Year_CA), aggr(-Max(CA_Month),Year_CA,Month_CA) ),'0')), (Num#(FirstSortedValue( aggr(Month_CA,Year_CA,Month_CA), aggr(-Max(CA_Month),Month_CA,Month_CA) ),'0')) )))'},

[ODS_GLOBAL.Type de pièce] ={'Avoir'}

>

Best regards