Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
timothyj
Creator
Creator

If vs Set Analysis Using Dimensions

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! 

Labels (2)
2 Solutions

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@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.

Capture.PNG

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

Capture.PNG

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Kushal_Chawda

@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])

View solution in original post

10 Replies
Taoufiq_Zarra
Master II
Master II

@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.

Capture.PNG

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

Capture.PNG

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

timothyj
Creator
Creator
Author

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

timothyj_0-1600439850796.png

 

Taoufiq_Zarra
Master II
Master II

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
timothyj
Creator
Creator
Author

This might view better.Might work better.Might work better.

timothyj
Creator
Creator
Author

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
];

Kushal_Chawda

@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])

View solution in original post

timothyj
Creator
Creator
Author

 
timothyj
Creator
Creator
Author

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!

Kushal_Chawda

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