Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
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))

Anonymous
Not applicable
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