Date Criteria in Expression / Set Analysis

[#1]

I thought I had started to understand the use of date formats in Qlikview, but this has me stumped.

Why does this work in a chart expression:

sum({<[NU]={'New'},[Sale Type]={'Retail'},DataType={'Budget'},InvMonthYear={'Mar-17'}>}[Retail Units])

where InvMonthYear is Date(makedate(mid([Invoice Date],7,4),mid([Invoice Date],4,2),1),'MMM-YY') as [InvMonthYear]  when created in the LOAD script,

But this doesn't:

sum({<[NU]={'New'},[Sale Type]={'Retail'},DataType={'Budget'},InvMonthYear={$(vActualMonthYear)}>}[Retail Units])

where vActualMonthYear is a variable:

SET vActualMonthYear = Date(MonthStart(today()),'MMM-YY');

Any assistance much appreciated

[marcus_sommer]

Try it with:

SET vActualMonthYear = Date(floor(MonthStart(today())),'MMM-YY');

because Monthstart() returned a timestamp and not a date.

- Marcus

[rubenmarin · Accepted solution]

Hi Tim, I think your expression needs an '=' in variable or in dollar-expansion, and simple quotes in set analysis:

sum({<[NU]={'New'},[Sale Type]={'Retail'},DataType={'Budget'},InvMonthYear={'$(=vActualMonthYear)'}>}[Retail Units])

or the '=' can be set in variable (simple quotes in set analysis are still needed):

SET vActualMonthYear = =Date(MonthStart(today()),'MMM-YY');

[sasiparupudi1]

Try putting this variable in a textbox and see what value is returned.

'$(=vActualMonthYear)'

Hth

[#5]

Thank you that worked. Not sure I quite understand why at the moment, but I'll just accept the result.

[#6]

I tried all those sorts of things.

What also confused me was I tried the if else condition:

if(InvMonthYear=$(vActualMonthYear),1,0) in a list box and it produced the result of 1 when the InvMonthYear field was Mar-17

So I assumed this should work in a set analysis.

[#7]

I'll give that a go.

Never seen floor used in relation to dates

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

[rubenmarin]

Hi Tim, trying to explain... We want InvMonthYear={'Mar-17'}

If we don't set the simple quotes we only get:

InvMonthYear={Mar-17}

This syntaxis seems is telling: "substract 17 from the field Mar", so we need the single quotes to look for the string 'Mar-17'.

If we don't set an equal the variable expression is not evaluated, it's just a text, it will be expanded as:

InvMonthYear={'Date(MonthStart(today()),'MMM-YY')'}

Wich can't be evaluated. Adding an equal sign before it's telling to evalue the expression and after that pass the result to the $-expansion

[rubenmarin]

In this case it's expanded as:

if(InvMonthYear=Date(MonthStart(today()),'MMM-YY'),1,0)

It's a correct expression that can work.