Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis to get max Month per year in a chart.

How can I do this in set analysis? I want to get the latest month per year.

 

YearMax(Month)AmountComment
201512$100.00Amount for Dec 2015
20167$200.00Amount for July 2015
2017-$0.00

If I use this expression the max month is always 12.


SUM({< Month= {"$(=max({Month))"}>}  Amount)


Result.


YearMax(Month)Amount
201512$100.00
201670
2017-0



expected result should be this.


YearMax(Month)Amount
201512$100.00
20167$200.00
2017-$0.00


15 Replies
Not applicable
Author

i need to have another column which gets the total for the year (Profit).


The column Outstanding needs to get the sum of the latest month for the year.  Or the max month depends on the user selection. 

LOAD * INLINE [

    Year, Month, Outstanding, Profit

    2015, 1, 300, 100

    2015, 2, 1200, 100

    2015, 3, 300, 100

    2015, 10, 200, 100

    2015, 11, 200, 100

    2015, 12, 200, 100

    2015, 12, 700, 100

    2016, 1, 200, 200

    2016, 2, 600, 200

    2016, 3, 100, 200

    2016, 3, 200, 200

];

Data is this...

   

   

    

Data
YearMonthOutstandingSales
20151300100
20152120100
20153300100
201510200100
201511200100
201512200100
201512700100
20161200200
20162600200
20163100200
20163200200

Expected Result

Year

Month

(hide Month if possible)

  Outstanding (Max Month)Total Sales
201512900700
20163300800
Not applicable
Author

i need to have another column which gets the total for the year (Profit).


The column Outstanding needs to get the sum of the latest month for the year.  Or the max month depends on the user selection.

  

LOAD * INLINE [

    Year, Month, Outstanding, Profit

    2015, 1, 300, 100

    2015, 2, 1200, 100

    2015, 3, 300, 100

    2015, 10, 200, 100

    2015, 11, 200, 100

    2015, 12, 200, 100

    2015, 12, 700, 100

    2016, 1, 200, 200

    2016, 2, 600, 200

    2016, 3, 100, 200

    2016, 3, 200, 200

];

Data is this...

  

  

   

Data
YearMonthOutstandingProfit
20151300100
20152120100
20153300100
201510200100
201511200100
201512200100
201512700100
20161200200
20162600200
20163100200
20163200200

Expected Result

Year

Month

(hide Month if possible)

  Outstanding (Max Month)Total Profit
201512900700
20163300800
tresesco
MVP
MVP

Try:

FirstSortedValue(Aggr(Sum(Outstanding),Year,Month), -Aggr(Month, Year, Month))

Untitled.png

Please be careful of marking the 'correct answer' the right one, so that other people searching for similar solution is not misdirected.

View solution in original post

xufei123
Partner
Partner

See attached file Milan. It is a straight able so you can easily hide any column you want

Fei

Not applicable
Author

Thans Tres!!!

This is really a big help.

chaitragud
Partner
Partner

Thank you so much, It helped me a lot