5 Replies Latest reply: May 15, 2018 6:26 PM by youssef belloum

# Having an issue aggregating by Year_Month

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

• ###### Re: Having an issue aggregating by Year_Month

Hi,

try this:

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

• ###### Re: Having an issue aggregating by Year_Month

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

• ###### Re: Having an issue aggregating by Year_Month

you're welcome good luck

• ###### Re: Having an issue aggregating by Year_Month

May be this

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

• ###### Re: Having an issue aggregating by Year_Month

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.