topic How to use set analysis under set analysis in QlikView

How to use set analysis under set analysis

varunreddy — Sat, 16 Nov 2024 04:05:23 GMT

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!

Re: How to use set analysis under set analysis

marcus_sommer — Thu, 21 Mar 2019 14:45:12 GMT

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

- Marcus

Re: How to use set analysis under set analysis

daanciorea — Thu, 21 Mar 2019 14:53:44 GMT

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.

Re: How to use set analysis under set analysis

Andrew_Incite — Thu, 21 Mar 2019 15:16:56 GMT

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 🙂

Re: How to use set analysis under set analysis

varunreddy — Thu, 21 Mar 2019 15:45:49 GMT

Thanks Dan for your quick reply !

Re: How to use set analysis under set analysis

varunreddy — Thu, 21 Mar 2019 15:47:48 GMT

Thanks Marcus for the quick reply !

Re: How to use set analysis under set analysis

varunreddy — Thu, 21 Mar 2019 15:50:21 GMT

Hi Andrew,

Thanks for the quick response!