topic If vs Set Analysis Using Dimensions in QlikView

If vs Set Analysis Using Dimensions

timothyj — Fri, 15 Nov 2024 23:57:21 GMT

Pretty sure this is not possible, but wanted to double check.

My chart expression is:

=Sum(If([END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR],[MEMBERSHIP FEE],0))

Making a chart where [MONTH YEAR] is the dimension to track memberships over time. This works. I cannot do this in Set Analysis, can I?

A similar count expression that will work:

=Count(If([END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR], [MEMBERSHIP NUM]))

Again, there is not a Set Analysis expression that will work.

Googling the forum suggests this is because Set Analysis only calculates once per chart?

Thanks!

Re: If vs Set Analysis Using Dimensions

Taoufiq_Zarra — Fri, 18 Sep 2020 14:04:14 GMT

@timothyj normally yes Set Analysis only calculates once per chart ! but there is an alternative if I understood well the model.

suppose I have this sample, I know you have dates, it's the same logic.

if I use your expression with if I have the output :

I can get the same results with this set analysis :

=Sum({<[MEMBERSHIP ID]={"=[END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR]"}>} [MEMBERSHIP FEE])

output :

Re: If vs Set Analysis Using Dimensions

timothyj — Fri, 18 Sep 2020 14:39:05 GMT

With more data it does not work. Unsure why. I thought along those lines.

Re: If vs Set Analysis Using Dimensions

Taoufiq_Zarra — Fri, 18 Sep 2020 14:42:32 GMT

@timothyj can you share the sample in Excel format for example ?

Re: If vs Set Analysis Using Dimensions

timothyj — Fri, 18 Sep 2020 14:42:39 GMT

This might view better.Might work better.

Re: If vs Set Analysis Using Dimensions

timothyj — Fri, 18 Sep 2020 14:44:50 GMT

LOAD * INLINE [
MEMBERSHIP ID, END DATE, MONTH YEAR, START DATE, MEMBERSHIP FEE
101, 2/1/2001, 1/1/2001, 1/1/2001, 10
102, 5/1/2001, 1/1/2001, 1/1/2001, 10
103, 5/1/2001, 1/1/2001, 1/1/2001, 10
104, 2/1/2001, 1/1/2001, 1/1/2001, 10
102, 5/1/2001, 2/1/2001, 1/1/2001, 11
103, 5/1/2001, 2/1/2001, 1/1/2001, 11
105, 3/1/2001, 2/1/2001, 2/1/2001, 11
102, 5/1/2001, 3/1/2001, 1/1/2001, 12
103, 5/1/2001, 3/1/2001, 1/1/2001, 12
106, 4/1/2001, 3/1/2001, 3/1/2001, 12
107, 5/1/2001, 3/1/2001, 3/1/2001, 12
102, 5/1/2001, 4/1/2001, 1/1/2001, 13
103, 5/1/2001, 4/1/2001, 1/1/2001, 13
107, 5/1/2001, 4/1/2001, 3/1/2001, 13
108, 5/1/2001, 4/1/2001, 4/1/2001, 13
];

Re: If vs Set Analysis Using Dimensions

Kushal_Chawda — Fri, 18 Sep 2020 14:50:55 GMT

@timothyj to work this properly in set analysis you may need to evaluate your set expression on primary key of your data. So if you already have primary key field in your data you can refer to that in set analysis like below

=Count({< Primary_Key ={"=[END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR]"}>}[MEMBERSHIP NUM])

If you don't have primary key and unsure of what is the primary key then you can simply create the key in load script with simple rowno() function like below

LOAD ...

rowno() as Primary_Key

FROM table;

You can now refer this primary key in set analysis as same way described above. You can similarly use it for sum function as well

=sum({< Primary_Key ={"=[END DATE]>[MONTH YEAR] And [START DATE]<=[MONTH YEAR]"}>}[MEMBERSHIP FEE])

Re: If vs Set Analysis Using Dimensions

timothyj — Fri, 18 Sep 2020 14:54:25 GMT

Re: If vs Set Analysis Using Dimensions

timothyj — Fri, 18 Sep 2020 15:00:44 GMT

Bingo, that made Taoufiq 's formula work. I will add that to the much larger data set that loops through way to many months.

Who gets the correct solution tag? We have a joint effort! More questions!

Re: If vs Set Analysis Using Dimensions

Kushal_Chawda — Fri, 18 Sep 2020 15:10:34 GMT

@timothyj you can mark as many replies as solution and it's your choice which one to mark. 😊

Re: If vs Set Analysis Using Dimensions

timothyj — Fri, 18 Sep 2020 18:57:13 GMT

Teamwork! Thank you Taoufiq & Kush! I ran it through the big data set adding the RowNo() Key and had success!

Thank you both for helping folks out!