Having an issue aggregating by Year_Month

[radmin5253]

Given the following data set I am trying to use Qlik Sense to come up with a formula that can be used in a chart or table that will givem the Number of Discussions for each Month by State based upon the Max Index.

Discussions	Year_Month	State	Index
3	2018-Jan	NC	1
3	2018-Jan	NC	2
4	2018-Jan	NC	3
4	2018-Feb	NC	4
4	2018-Feb	NC	5
5	2018-Feb	NC	6
1	2018-Jan	Va	1
1	2018-Jan	Va	2
2	2018-Jan	Va	3
2	2018-Feb	Va	4
3	2018-Feb	Va	5
4	2018-Feb	Va	6
0	2018-Jan	Ca	1
1	2018-Jan	Ca	2
2	2018-Jan	Ca	3
3	2018-Feb	Ca	4
4	2018-Feb	Ca	5
5	2018-Feb	Ca	6

So when it is all said and done I will see

NC 2018-Jan 4

NC 2018-Feb 5

Va 2018-Jan 2

Va 2018-Feb 4

Ca 2018-Jan 2

Ca 2018-Feb 5

I have tried the following but it does not work:

IF(Max(Index), Aggr(Sum(Discussions), [Year_Month],State))

This gets me close but it seems to ignore the IF(Max(Index)) portion and just sums up all the discussions. Can anyone point me in the right direction. Thanks

Brian

[YoussefBelloum · Accepted solution]

Hi,

try this:

=max(aggr(sum(Discussions),Year_Month,State,Index))

[sunny_talwar]

May be this

Sum(Aggr(If(Index = Max(TOTAL <State, Year_Month> Index), Discussions), State, Year_Month, Index))

[radmin5253]

Youssef thanks this worked perfectly I can not believe it was this simple.

[radmin5253]

Sunny thanks for the formula it did not solve this particular issue for me but I do see another use for it in a seperate application I am working on.

[YoussefBelloum]

you're welcome good luck