topic Re: variance in expression from date in QlikView

variance in expression from date

capriconuser — Mon, 18 Mar 2019 05:48:01 GMT

I have a date like this

AS_Date

and from date i extract month , year and quarter like this

year(As_date) Year,

Month(As_date) MOnth,

'Q' & ceil(month(As_Date)/3) as Quarter

and i have a expression like this

Num(count(distinct{<Code={'34'}>}Amount))

so i want when user select suppose they select 2 jan then i want to get variance like suppose on 1 jan is 1000 and in 2 jan 2000 so want to get variance 1 jan - 2 jan e.g. 1000-2000 =1000

whatever the date is selected that date minus previous date

and for month suppose jan is selected than jan month - dec month like selected month and previous month .. same goes for year and quarter

so how i do that in expression

Re: variance in expression from date

tresB — Mon, 18 Mar 2019 06:28:09 GMT

You can try like:

count(distinct{<AS_Date={'$(=Date(Max(AS_Date)))'},Code={'34'}>}Amount)
-
count(distinct{<AS_Date={'$(=Date(Max(AS_Date)-1))'},Code={'34'}>}Amount)

Similarly for month and year

Re: variance in expression from date

Shubham_Deshmukh — Mon, 18 Mar 2019 06:57:15 GMT

Hi @tresB ,

What would be the condition if data doesn't have data on (date - 1) day? Means before 2 jan directly on 31 Dec.

Re: variance in expression from date

capriconuser — Mon, 18 Mar 2019 07:00:27 GMT

is this possible to do that in 1 line like something this
count(distinct{<AS_Date={'$(=Date(Max(AS_Date) - "here minus thing" ))'},Code={'34'}>}Amount)

is that possible

Re: variance in expression from date

tresB — Mon, 18 Mar 2019 07:02:02 GMT

That way, you would not get your desired result.

Re: variance in expression from date

tresB — Mon, 18 Mar 2019 07:07:59 GMT

Hi Shubham,

In that case, you could probably put additional condition in set to filter in only non-zero amounts and get the 2nd max date , like:

count(distinct{<AS_Date={'$(=Date(Max({<Amount={">0"}>}AS_Date,2)))'}
,Code={'34'}>}Amount)