topic Re: Set analysis with criteria that includes timestamp in QlikView

Set analysis with criteria that includes timestamp

n1ef5ng1 — Wed, 21 Dec 2016 03:41:57 GMT

Hi everyone, I have a question. I cant do it on dimension so is purely on set analysis expressions.

sum({<Day(Date1)=(Day(Date2))}>} Revenue)

Basically i want to sum the revenue if the DAY for Date 1 and Date 2 are on the same DAY.

i.e

Customer booked a hotel room on nov 11 (Date1) and checking in on the date itelf (nov 11), i would want to count these on the same date check in itself on the revenue.

I cant do in script nor dimension.

How should i do

Re: Set analysis with criteria that includes timestamp

sushil353 — Wed, 21 Dec 2016 03:51:02 GMT

hi,

you can use if(Date1=Date2,sum(Revenue),null())

Re: Set analysis with criteria that includes timestamp

shraddha_g — Wed, 21 Dec 2016 05:23:16 GMT

try

sum(if(day(Date1) = day(Date2),Revenue)

Re: Set analysis with criteria that includes timestamp

avinashelite — Wed, 21 Dec 2016 05:35:38 GMT

simple could be to add a flag in the script so that your set analysis becomes simple and improve the performance

if(Date1=Date2,1,0) as Flag

sum({<Flag={1}>}Revenue)

Re: Set analysis with criteria that includes timestamp

Anonymous — Wed, 21 Dec 2016 07:03:01 GMT

Hi Ben,

Find the attached QVW file, it may help you.

R/Murali