Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aniruddhyadutta
Contributor

tricky date condition in set analysis

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()))

hours.PNG

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()))

max date.PNG

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.

max date.PNG

can someone please help me out!

Thanks,

Aniruddhya

11 Replies

Re: tricky date condition in set analysis

How do you define your variable v5?

aniruddhyadutta
Contributor

Re: tricky date condition in set analysis

Hi Sunny,

the def is as below

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

AD

robin_hausdoerfer
Valued Contributor III

Re: tricky date condition in set analysis

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()))

Re: tricky date condition in set analysis

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()))

robin_hausdoerfer
Valued Contributor III

Re: tricky date condition in set analysis

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
Contributor

Re: tricky date condition in set analysis

Hi Sunny,

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

AD

aniruddhyadutta
Contributor

Re: tricky date condition in set analysis

Hi Rob,

Unfortunately this didn't worked  out either..

AD

aniruddhyadutta
Contributor

Re: tricky date condition in set analysis

even if I hardcode its not working

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

Re: tricky date condition in set analysis

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?