topic Re: Date Criteria in Expression / Set Analysis in QlikView

Date Criteria in Expression / Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Date Criteria in Expression / Set Analysis

marcus_sommer — Sat, 11 Mar 2017 17:32:33 GMT

Try it with:

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

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

- Marcus

Re: Date Criteria in Expression / Set Analysis

rubenmarin — Sat, 11 Mar 2017 18:45:19 GMT

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');

Re: Date Criteria in Expression / Set Analysis

sasiparupudi1 — Sat, 11 Mar 2017 19:41:14 GMT

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

'$(=vActualMonthYear)'

Hth

Re: Date Criteria in Expression / Set Analysis

Sat, 11 Mar 2017 20:50:51 GMT

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

Re: Date Criteria in Expression / Set Analysis

Sat, 11 Mar 2017 20:54:29 GMT

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.

Re: Date Criteria in Expression / Set Analysis

Sat, 11 Mar 2017 20:55:51 GMT

I'll give that a go.

Never seen floor used in relation to dates

Re: Date Criteria in Expression / Set Analysis

oknotsen — Sun, 12 Mar 2017 10:06:14 GMT

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 .

Re: Date Criteria in Expression / Set Analysis

rubenmarin — Sun, 12 Mar 2017 21:18:00 GMT

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

Re: Date Criteria in Expression / Set Analysis

rubenmarin — Sun, 12 Mar 2017 21:20:44 GMT

In this case it's expanded as:

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

It's a correct expression that can work.