Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

belguith
New Contributor II

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

Could Some one help please,

Thank you verry much.

1 Solution

Accepted Solutions
belguith
New Contributor II

Re: Complex Conditianal Sum with Max ,Agg  row fields

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

3 Replies
MVP
MVP

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

belguith
New Contributor II

Re: Complex Conditianal Sum with Max ,Agg  row fields

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

MVP
MVP

Re: Complex Conditianal Sum with Max ,Agg row fields

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

Best,

Sunny

Community Browser