11 Replies Latest reply: Nov 4, 2016 1:31 AM by Vegar Lie Arntsen

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

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

• ###### Re: tricky date condition in set analysis

How do you define your variable v5?

• ###### 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

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

• ###### 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.

• ###### Re: tricky date condition in set analysis

Hi Rob,

Unfortunately this didn't worked  out either..

AD

• ###### Re: tricky date condition in set analysis

Hi Sunny,

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

AD

• ###### 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?

• ###### Re: tricky date condition in set analysis

Aniruddhya Dutta wrote:

...

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

I believe you got the syntax wrong in this test. Try:

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

• ###### Re: tricky date condition in set analysis

I used QlikView 12 SR 5 for my example.

If the hardcoded variant doesn't work, it looks like a bug for me.

You could try QlikView 12 Personal Edition for testing. It is basically the same.

If you get another result, then it should be a bug....