Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
try this:
=max(aggr(sum(Discussions),Year_Month,State,Index))
May be this
Sum(Aggr(If(Index = Max(TOTAL <State, Year_Month> Index), Discussions), State, Year_Month, Index))
Youssef thanks this worked perfectly I can not believe it was this simple.
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.
you're welcome good luck