Aggr and set analysis

[kevincase]

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

[sinanozdemir]

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.

[kevincase]

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]

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]

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

I will try to come up with a better expression.

[sinanozdemir]

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]

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]

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

Thanks