topic Set Analysis condition on variables in QlikView

Set Analysis condition on variables

Mon, 31 Aug 2015 13:10:46 GMT

Hi,

I have this variable : vtodaymonth=MonthName(today())

And I want to create another variable that would count the non null date field given that these date's month match the current one.

So I created :

vdate1=count(distinct{<[monthDate1]={'=$(vDOtodaymonth)'}, [monthDate1]={'*'}-{''} >} [Report])

But I don't get any relevent results. Could you please help me?

Thank you very much

Re: Set Analysis condition on variables

sunny_talwar — Mon, 31 Aug 2015 14:14:59 GMT

Not sure what the first part of the set analysis is doing, but to handle the non null part, try this:

vdate1=count(distinct{<[monthDate1]={'=$(vDOtodaymonth)'}, [monthDate1]={'*?'}>} [Report])

Re: Set Analysis condition on variables

Tue, 01 Sep 2015 07:38:25 GMT

Thank you for your response Sunny T.

In the first part I just wanted to add a condition : only count the existing dates conditional on their months being the current one. Ex :if today is Sep 31st , vDOtodaymonth would be equal to "September" so I should be counting all date1 from September. Unfortunately QV doesn't seem to understand when I try to match a specific field value with a variable's result

Re: Set Analysis condition on variables

sunny_talwar — Tue, 01 Sep 2015 07:42:50 GMT

The format of your variable and the field needs to match in order for the set analysis to filter results. Does there format match-> monthDate1 and vDOtodaymonth?

Re: Set Analysis condition on variables

Tue, 01 Sep 2015 08:02:08 GMT

To be honst I have no idea. I just know that vDOtodaymonth give me"sept." now and monthDate1 's value for september is also sept.

Does it necessary means their format match?

Re: Set Analysis condition on variables

sunny_talwar — Tue, 01 Sep 2015 08:08:59 GMT

It may still not match if one of the two is a dual format month and other one is text. Can you share the script for where you create monthDate1 field in the script and also the variable definition for vDOtodaymonth?

Re: Set Analysis condition on variables

Tue, 01 Sep 2015 08:32:00 GMT

Where I created monthdate1 in the simplest way:

Report:

LOAD Month([D1]) as monthDate1

FROM

[0_OSR Global_20150706.xlsx]

(ooxml, embedded labels, table is PA)

and for the variable I just used the variable wizard and wrote =Month(today())

should I add a fomat condition on the variable vDOtodaymonth ?

Re: Set Analysis condition on variables

sasiparupudi1 — Tue, 01 Sep 2015 08:37:46 GMT

the format must match.

Please change your variable value

from

vtodaymonth=MonthName(today())

=Date(today(),'MMM')

hth

Sasi

Re: Set Analysis condition on variables

sunny_talwar — Tue, 01 Sep 2015 08:48:37 GMT

See if the first part of the set analysis work:

Count(DISTINCT {<[monthDate1]={'$(vDOtodaymonth)'}>} [Report])

Re: Set Analysis condition on variables

sasiparupudi1 — Tue, 01 Sep 2015 08:54:53 GMT

isnt your second part ,overriding the first part?

[monthDate1]={'*'}-{''}

your telling qlikview to select everything except blanks

Re: Set Analysis condition on variables

sunny_talwar — Tue, 01 Sep 2015 08:56:29 GMT

Hahahaha I never saw that the two fields are the same. Good catch

Re: Set Analysis condition on variables

Tue, 01 Sep 2015 09:09:37 GMT

Works perfectly now! thanks guys!

Re: Set Analysis condition on variables

sasiparupudi1 — Tue, 01 Sep 2015 09:13:28 GMT

Great, please select correct and any helpful answers and mark this thread closed