How to use set analysis under set analysis

[varunreddy]

DataTotal Issues by DateRequired OutputDesired output

Hi All,

I am trying to find the number of Id's that has "Open Date" in past 12 months from the "Date".

Here I am having three fields  Date, Open Date and Id. Date is when I extract the data, Open Date is when issue is opened and Id is an issue.

I have Date as dimension and trying to count the number of issues that has been opened in last one year. In this example, I have a date 1/1/2018, so I need to display the count of issues opened from 1/1/2017. The another date is 2/1/2018 and I need to display the count of issues opened from 2/1/2017.

Not sure where I am doing wrong. Please find the attached Spread sheet and QlikView document and please help me with this.

Expression I am using -

Count({<[Open Date] = {">=$(=Addmonths(max(Date),-12))"}>}Id)

Thanks in advance!

[marcus_sommer]

Try it with: Count({< Id = {"=[Open Date]>=addmonths(Date,-12)"}>} Id)

- Marcus

[daanciorea · Accepted solution]

Hi,

You can add a flag field like this:
DATA:
LOAD Date,
Id,
[Open Date],
If(num(Date - [Open Date]) <= 365, 1, 0) as Flag_Dif
FROM ...;

And use the follow expresion: Count({<Flag_Dif = {1}>}Id)

I hope solve your problem.

Dan.

[Andrew_Incite]

In this instance Set Analysis is only calculating once on the Open Date field. 

You can use the same logic in a conditional statement. 

Count(
IF([Open Date] >= AddMonths(Date,-12)
,Id))

This will give you what you expect 

Edit: Marcus's reply will also work just fine as well.  I find this to be a little simpler 🙂 

[varunreddy]

Thanks Dan for your quick reply !

[varunreddy]

Thanks Marcus for the quick reply !

[varunreddy]

Hi Andrew,

Thanks for the quick response!