Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

radmin5253
New Contributor III

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.

   

DiscussionsYear_MonthStateIndex
32018-JanNC1
32018-JanNC2
42018-JanNC3
42018-FebNC4
42018-FebNC5
52018-FebNC6
12018-JanVa1
12018-JanVa2
22018-JanVa3
22018-FebVa4
32018-FebVa5
42018-FebVa6
02018-JanCa1
12018-JanCa2
22018-JanCa3
32018-FebCa4
42018-FebCa5
52018-FebCa6

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

   

1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: Having an issue aggregating by Year_Month

Hi,

try this:

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

5 Replies
YoussefBelloum
Esteemed Contributor

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

May be this

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

radmin5253
New Contributor III

Re: Having an issue aggregating by Year_Month

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

radmin5253
New Contributor III

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.

YoussefBelloum
Esteemed Contributor

Re: Having an issue aggregating by Year_Month

you're welcome good luck

Community Browser