topic Set Analysis with Variables in QlikView

Set Analysis with Variables

Mon, 13 Jul 2009 23:57:59 GMT

Hey,
I have created variable called EndCompareDate that has a condition where it can only take date within the start of the year until the current date. (e.g. 01/2009, 02, 2009, 03/2009... etc). What I would like to do is when a user inputs a month/year into the variable, that input will change the parameter to the set analysis. This is what I have so far:

=(Count(ALL{<Dateentered = {EndCompareDate}>} Product))

The problem is that this returns 0, which clearly can't be because I ran the set analysis with EndCompareDate to be 06/2009 and I actually get a result. Can anyone see why this isn't working?

Thank you in advance.

Set Analysis with Variables

johnw — Tue, 14 Jul 2009 00:07:25 GMT

I would try to avoid using ALL in expressions. While it is still supported, I believe the new "standard" is to use {1} to indicate the set of all values. That gives us this:

count({1<Dateentered={EndCompareDate}>} Product)

I also can't remember if you can reference variables directly like that. You may need to do a dollar sign expansion on it:

count({1<Dateentered={$(=EndCompareDate)}>} Product)

Finally, I don't guarantee that the dollar sign expansion will give you a date in the same format as field "Dateentered". So you might need to format the result. If Dateentered was in MM/DD/YY format, for instance, you might need to do this:

count({1<Dateentered={$(=date(EndCompareDate,'MM/DD/YY'))}>} Product)

Set Analysis with Variables

Anonymous — Tue, 14 Jul 2009 00:43:34 GMT

I'm using variables in set analysis:
sum({1<"Fiscal Year"={$(PrevFY)}>} Amount)