variance in expression from date

[capriconuser]

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 

[tresesco]

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

[Shubham_Deshmukh]

Hi @tresesco ,

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

[capriconuser]

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

[tresesco]

That way, you would not get your desired result.

[tresesco]

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)