tricky date condition in set analysis

[aniruddhyadutta]

Hi All,

I am using a expression in set analysis where I want to show the cumulative sum of alert_count for  the present hour,present-1,present- 2 hour .here the variables $v2, $v3 and $v4 are calculating as hours.This is coming as fine

RangeSum(Above(Sum({$< Hour_link={'$(v2)','$(v3)','$(v4)'}>} ALERT_Count),0,RowNo()))

But the problem is that I want to have my chart selected for the max date  for which I used

RangeSum(Above(Sum({$< Hour_link={'$(v2)','$(v3)','$(v4)'},Dates={'$(v5)'}>} ALERT_Count),0,RowNo()))

my variable i.e $v5 is fetching as expected date which is the maximum of my data set.I just wanted to use that condition Dates=$v5

in my database datatype of Dates filed is date only.

can someone please help me out!

Thanks,

Aniruddhya

[sunny_talwar]

How do you define your variable v5?

[aniruddhyadutta]

Hi Sunny,

the def is as below

set v5=max(date(date#(left(DATETIME,8),'MMDDYYYY'),'YYYY-MMM-DD'));

AD

[Anonymous]

please try

let v5=max(date(date#(left(DATETIME,8),'MMDDYYYY'),'YYYY-MMM-DD'));

or

RangeSum(Above(Sum({$< Hour_link={'$(v2)','$(v3)','$(v4)'},Dates={'$(=v5)'}>} ALERT_Count),0,RowNo()))

[sunny_talwar]

When you create a text box with v5 as your expression, do you see the max date? If you do, then all your might need to do is to add an equal sign here and replace single quotes with double quotes in your modifier:

RangeSum(Above(Sum({$< Hour_link={'$(v2)','$(v3)','$(v4)'},Dates={"$(=v5)"}>} ALERT_Count),0,RowNo()))

[Anonymous]

another try:

RangeSum(Above(Sum({$< Hour_link={'$(v2)','$(v3)','$(v4)'},Dates={'$(=Date($(=v5)))'}>} ALERT_Count),0,RowNo()))

@Sunny T: your solution doesn't work for me, also with double quotes, I have to use the Date() function.

[aniruddhyadutta]

Hi Sunny,

Unfortunately I had  tried this approach but did not worked..

AD

[aniruddhyadutta]

Hi Rob,

Unfortunately this didn't worked  out either..

AD

[aniruddhyadutta]

even if I hardcode its not working

RangeSum(Above(Sum({$< Hour_link={'$(v2)','$(v3)','$(v4)'}>} ALERT_Count,Dates={'2016-Oct-26'}),0,RowNo()))

[sunny_talwar]

What is Dates field format within Qlik Sense? Create a filter object and see what format does it come in as? Also check if it is read as a date or not. These are some good links to read about dates

Get the Dates Right

Why don’t my dates work?