Complex Conditianal Sum with Max ,Agg row fields

[Anonymous]

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])

Could Some one help please,

Thank you verry much.

[sunny_talwar]

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))

[Anonymous · Accepted solution]

Thank you Sunny, i'll test your answer right now.
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

[sunny_talwar]

Awesome, I am glad you were able to figure this out. Please close the thread by marking your own response as correct.

Best,

Sunny