topic Re: Date Conditional Sum in QlikView

Date Conditional Sum

Mon, 27 Apr 2015 11:26:55 GMT

Hi all,

I am trying to set up a conditional expression that fulfills the following requirements:

-If today is a Monday, it will automatically sum up the total for the period Friday, Saturday and Sunday preceding.

-It is independent of all selections therefore {1} applies.

-If today is not a Monday, then it will just show the total for the previous day only.

The conditional expression that I came up with is as follows:

if((Today=('Mon')),

sum({1<Date={'>=$(=Only(Today())-3)<$(=Only(Today()))'}>}NoA),

sum({1<Date={'$(=Date(Today()-1))'}>}NoA))

However, when the day is a Monday (i.e. today) this is still just calculating the sum for the day before, can someone please advise what is wrong with my expression?

Note that NoA is the fieldname of what I am trying to add up.

Thanks in advance for your kind help.

Armend

Re: Date Conditional Sum

Anonymous — Mon, 27 Apr 2015 11:41:33 GMT

hi use

=if(weekday(Today())='Mon', sum({1<Date={'>=$(=Only(Today())-3)<$(=Only(Today()))'}>}NoA),sum({1<Date={'$(=Date(Today()-1))'}>}NoA)))

Thanks

BKC

Re: Date Conditional Sum

Anonymous — Mon, 27 Apr 2015 11:43:24 GMT

ur if statement is wrong

if((Today=('Mon'))

you need to use

if(weekday(Today())='Mon',

Thanks

BKC

Re: Date Conditional Sum

Gabriel — Mon, 27 Apr 2015 11:43:45 GMT

Hi,

What I can see there is, in your SA, because you want to search your expression should be in double quote, I think your current expression is using single quote. And your weekday function as well needs changing to this

=If((WeekDay(Today()) ='Mon',

sum({1<Date={“>=$(=(Today())-3)<$(=(Today()))”}>}NoA),

sum({1<Date={“$(=Date(Today()-1))”}>}NoA))

Re: Date Conditional Sum

Mon, 27 Apr 2015 11:52:43 GMT

Hi BKC,

Thank you for your response, however this is how my expression looked originally and what it does is that it calculates the total to date i.e. not just for the preceding three days but a sum of all records. I need it to add only the preceding three days if today's date is a Monday.

Any further advice would be greatly appreciated.

Thanks,

Armend

Re: Date Conditional Sum

Anonymous — Mon, 27 Apr 2015 12:04:13 GMT

=If((WeekDay(Today()) ='Mon',

sum({1<Date={“>=$(=(Today())-3)<$(=(Today()))”}>}NoA),

sum({1<Date = {"<=$(=Today()) – 1)"}>}NoA))

Re: Date Conditional Sum

Mon, 27 Apr 2015 14:00:31 GMT

Thanks BKC,

I managed to figure out where my error was, please see amended statement which now works fine.

=if(WeekDay(Today())='Mon',

sum({1<Date={'>=$(=Date(Today()-3))<$(=Date(Today()))'}>}NoA),

sum({1<Date={'$(=Date(Today()-1))'}>}NoA)

)

Thank you for your assistance.

Regards,

Armend

Re: Date Conditional Sum

Anonymous — Mon, 27 Apr 2015 14:08:10 GMT

Good !!