topic Multiple Dates within Expression in QlikView

Multiple Dates within Expression

Anonymous — Thu, 11 May 2017 16:07:32 GMT

Is it possible to create an expression that contains the equivalent of an OR statement

SQL would be;

select sum(sales)

Where (Yearfield = 2017 AND Datefield <= '2017/05/10')

OR (Yearfield = 2016 AND Datefield <= '2016/05/10')

OR (Yearfield = 2015 AND Datefield <= '2015/05/10')

So I would end up with numbers for YTD 3 year comparison.

I want to use this to calculate an average so I can see how the average plots year on year after the same period.

Re: Multiple Dates within Expression

Anonymous — Thu, 11 May 2017 16:14:36 GMT

you could try

...

where yeartodate(Datefield,0) or yeartodate(Datefield,-1) or yeartodate(Datefield,-2) ...

You could also do this in a loop in combination with concatenate

Re: Multiple Dates within Expression

Sergey_Shuklin — Thu, 11 May 2017 16:17:56 GMT

Hi, John!

Would it be usefull?

=sum(if(Yearfield = 2017 AND Datefield <= '2017/05/10',sales,0))+

sum(if(Yearfield = 2016 AND Datefield <= '2016/05/10',sales,0))+

sum(if(Yearfield = 2015 AND Datefield <= '2015/05/10',sales,0))

Re: Multiple Dates within Expression

Anonymous — Mon, 15 May 2017 08:54:13 GMT

Hi,

Thanks for your replies, unfortunately neither resolved my issue

I've tried another example below;

=sum({<[Cover]={'1'}

AND (

([Financial Year]={"$(v.Date.Current.Year)"},[Transaction Date]={"<=$(v.Date.Yesterday)"})

OR ([Financial Year]={"$(v.Date.Year.Prior)"},[Transaction Date]={"<=$(v.Date.Date.TodayLastYear)"})

OR ([Financial Year]={"$(v.Date.Year.Prior2)"},[Transaction Date]={"<=$(v.Date.Date.Today2YearsAgo)"})

)

>}[Quantity Sold])

This would produce a simple chart as in the example below however the example below has the 3 date ranges as separate expressions, I need all 3 as 1 expression as I need to over lay a line graph, which is currently resulting in 3 separate dots rather than a single trend line.

Re: Multiple Dates within Expression

Anonymous — Mon, 15 May 2017 09:49:17 GMT

I have been able to find the solution '+' is OR

For;

(Cover=1 AND Year = Current )

OR (Cover=1 AND Year = Previous AND date <= today last year)

OR (Cover=1 AND Year = 2 yrs ago AND date <= today 2 yrs ago)

Expression =;

=sum({<[Cover]={'1'},[Financial Year]={"$(v.Date.Current.Year)"}>

+<[Cover]={'1'} ,[Financial Year]={"$(v.Date.Year.Prior)"},[Transaction Date]={"<=$(v.Date.Date.TodayLastYear)"}>

+<[Cover]={'1'} ,[Financial Year]={"$(v.Date.Year.Prior2)"},[Transaction Date]={"<=$(v.Date.Date.Today2YearsAgo)"}>

}[Quantity Sold])