Set Analysis condition on variables

[#1]

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

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?

[#5]

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?

[sunny_talwar]

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?

[#7]

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 ?

[sasiparupudi1 · Accepted solution]

the format must match.

Please change your variable value

from

vtodaymonth=MonthName(today())

to

=Date(today(),'MMM')

hth

Sasi

[sunny_talwar]

See if the first part of the set analysis work:

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

[sasiparupudi1]

isnt your second part ,overriding the first part?

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

your telling qlikview to select everything except blanks