Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
belguith
Contributor II
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
Contributor II
Contributor II
Author

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

View solution in original post

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

belguith
Contributor II
Contributor II
Author

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