Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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)