topic Re: Set analysis to get max Month per year in a chart. in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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

Year	Max(Month)	Amount	Comment
2015	12	$100.00	Amount for Dec 2015
2016	7	$200.00	Amount for July 2015
2017	-	$0.00

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

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

Result.

Year	Max(Month)	Amount
2015	12	$100.00
2016	7	0
2017	-	0

expected result should be this.

Year	Max(Month)	Amount
2015	12	$100.00
2016	7	$200.00
2017	-	$0.00

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

Chanty4u — Wed, 13 Jul 2016 07:21:18 GMT

try

Firstsortedvalue(Month,-Amount)

Firstsortedvalue(Amount,-Month)

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

tresB — Wed, 13 Jul 2016 07:33:36 GMT

Set analysis would not work here since it doesn't evaluate row-wise but once for a chart.

Try like:

FirstSortedValue(Aggr(Sum(Amount),Month), -Month)

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

Thu, 14 Jul 2016 03:25:09 GMT

I tried getting the sum. but it's returning null. Not sure why it does not work in my data,

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

Thu, 14 Jul 2016 04:40:21 GMT

i have multiple rows Amount with month = Max month. Looks like this only works with one row as max Month.

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

NZFei — Thu, 14 Jul 2016 04:50:11 GMT

Check this out.

Dimension: Year, Month

Expression: if(Month=max(total <Year> Month),1,0)*sum(Sales)

Fei

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

Thu, 14 Jul 2016 04:53:26 GMT

Dude you're the best! high 5!

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

Thu, 14 Jul 2016 05:42:52 GMT

I tried now showing the Month field but it does not work. do you have other solution for this?

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

tresB — Thu, 14 Jul 2016 07:00:01 GMT

If you have multiple rows of Amount against max month, what do you want to do with them? Should they be summed up? If yes, my proposed solution above works that way. If the Amount values are just repeat (same) you can simply use Distinct like:

FirstSortedValue(Distinct Amount, -Month)

If possible, post a sample qvw.

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

NZFei — Thu, 14 Jul 2016 22:41:44 GMT

What is the problem now?

I have added some more lines and it is still working for me.

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

Fri, 15 Jul 2016 03:45:33 GMT

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
Year	Month	Outstanding	Sales
2015	1	300	100
2015	2	120	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
Expected Result
Year	Month (hide Month if possible)	Outstanding (Max Month)	Total Sales
2015	12	900	700
2016	3	300	800

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

Tue, 19 Jul 2016 04:15:21 GMT

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
Year	Month	Outstanding	Profit
2015	1	300	100
2015	2	120	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
Expected Result
Year	Month (hide Month if possible)	Outstanding (Max Month)	Total Profit
2015	12	900	700
2016	3	300	800

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

tresB — Tue, 19 Jul 2016 07:33:21 GMT

Try:

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

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

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

NZFei — Tue, 19 Jul 2016 20:57:14 GMT

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

Fei

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

Mon, 25 Jul 2016 01:29:23 GMT

Thans Tres!!!

This is really a big help.

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

chaitragud — Thu, 12 Dec 2019 08:19:48 GMT

Thank you so much, It helped me a lot