Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kevincase
Contributor II

Aggr and set analysis

I have been trying to figure out a way to get the measure that I need for my pivot table without creating a new field in the load script.  I was hoping to accomplish this via the AGGR function and set analysis.

What I'm trying to do is Sum(IDCount) where the earliest accounting date falls between a starting and ending date.  Consider the following sample data:

   

IDDivisionBranchAccountingDateIDCount
1126/1/20151
1126/10/20151
2124/1/20151
2126/12/20151
3126/15/20151
3126/18/20151
4136/2/20151
5133/25/20151

If my starting date is 6/1/2015 and my ending date is 6/30/2015, I expect three rows to match my criteria (highlighted in yellow).  I would like to use this measure in a pivot table that has the dimensions of Division and Branch.  I would like to display the following:

   

DivisionBranchTotal
122
131

Thinking in set analysis mode, I would like to do the following:

Sum({1<Min([AccountingDate])={">=$(=Date($(v3)))<=$(=Date($(v4)))"}>}[IDCount])

However, this is not possible.  I was thinking that if I could use AGGR, it may be possible.  Something like:

Sum({1<Aggr(Min([AccountingDate]), [Division], [Branch], [ID])={">=$(=Date($(v3)))<=$(=Date($(v4)))"}>}[IDCount])

Is this possible?  Anyone have any suggestions as to how this can be achieved?  Not sure if I can apply set analysis over the field that is being aggregated.

Thanks.

Kevin

7 Replies
sinanozdemir
Valued Contributor III

Re: Aggr and set analysis

Hi Kevin,

I had to do a small trick to get this working.

I brought in Division, Branch, and ID dimensions first:

Capture.PNG

Then in the presentation tab, I hid the ID dimension:

Capture2.PNG

Lastly, I used the below expression to come up with your desired results.

Capture3.PNG

Hope this helps.

kevincase
Contributor II

Re: Aggr and set analysis

Sinan,

Unfortunately, your solution is using Qlik View.  I am using Qlik Sense and do not the option to hide a dimension/column.  I will look at trying to use your expression with hopes that it points me in a new direction.

sinanozdemir
Valued Contributor III

Re: Aggr and set analysis

Hi Kevin,

I have just realized that your requirements were different than what I interpreted so the above solution won't work.

Sorry, I will try to rectify it.

Thanks

sinanozdemir
Valued Contributor III

Re: Aggr and set analysis

Anyway, it is not a good solution for QlikView either.

I will try to come up with a better expression.

sinanozdemir
Valued Contributor III

Re: Aggr and set analysis

Hi Kevin,

Did you have any luck with this?

I was thinking of a set analysis like the below:

Sum({<AccountingDate = {">=$(=Aggr(Min(AccountingDate), ID, Branch))<$(=Aggr(Max(AccountingDate), ID, Branch))"}>} IDCount)

If this doesn't work, then unfortunately you may have to create Min and Max dates in your load script.

Thanks

kevincase
Contributor II

Re: Aggr and set analysis

Sinan,

I don't believe that this will work either.  The minimum Accounting Date must fall between two dates that are variables.

I appreciate your help.  As you said, it may be easier just to add the date in the load script.  I was trying to avoid this as the measure I am trying to create will probably never be used again other than on this pivot table.

Thanks again for your help.

Kevin

sinanozdemir
Valued Contributor III

Re: Aggr and set analysis

Let me know if you come with a solution that doesn't require creating additional dimensions in the load script.

Thanks