topic Re: Aggr and set analysis in App Development

Aggr and set analysis

kevincase — Wed, 08 Jul 2015 14:34:37 GMT

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:

ID	Division	Branch	AccountingDate	IDCount
1	1	2	6/1/2015	1
1	1	2	6/10/2015	1
2	1	2	4/1/2015	1
2	1	2	6/12/2015	1
3	1	2	6/15/2015	1
3	1	2	6/18/2015	1
4	1	3	6/2/2015	1
5	1	3	3/25/2015	1

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:

Division	Branch	Total
1	2	2
1	3	1

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

Re: Aggr and set analysis

sinanozdemir — Wed, 08 Jul 2015 18:13:43 GMT

Hi Kevin,

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

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

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

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

Hope this helps.

Re: Aggr and set analysis

kevincase — Wed, 08 Jul 2015 18:47:26 GMT

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.

Re: Aggr and set analysis

sinanozdemir — Wed, 08 Jul 2015 18:48:09 GMT

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

Re: Aggr and set analysis

sinanozdemir — Wed, 08 Jul 2015 18:49:12 GMT

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

I will try to come up with a better expression.

Re: Aggr and set analysis

sinanozdemir — Thu, 09 Jul 2015 19:22:14 GMT

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

Re: Aggr and set analysis

kevincase — Thu, 09 Jul 2015 20:14:40 GMT

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

Re: Aggr and set analysis

sinanozdemir — Thu, 09 Jul 2015 20:28:28 GMT

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

Thanks