topic Re: Sum prev date. Ignore holidays and weekends in QlikView

Sum prev date. Ignore holidays and weekends

Thu, 23 Apr 2015 13:11:46 GMT

Hi,

I need to sum data from the prev open date.

Problems occur when:

1. Holidays.

2. When I select a month, the very last sum from the prev month will not show.

In my table I've now added the holidays, but Im not sure how to implement in qlikView.

Pls find attached example QV-file

Expected result as per below when choosing january.

Regards,

Olle

Re: Sum prev date. Ignore holidays and weekends

sunny_talwar — Thu, 23 Apr 2015 13:14:12 GMT

Man you are back, didn't we solve something similar couple of month ago? Hahahaha

Re: Sum prev date. Ignore holidays and weekends

Thu, 23 Apr 2015 13:16:21 GMT

Yes Im back alright

Tried to use our prev solution but I could not get it to work.

Im figuring, using a list of holidays would be more convenient in a sense?

Re: Sum prev date. Ignore holidays and weekends

sunny_talwar — Thu, 23 Apr 2015 13:19:40 GMT

I guess, I am thinking if I should jump in to look at it or not. I have lot of stuff to work on today and knowing you, your query might not be an easy one .

I will wait and see if someone else chips in, else I will try to help later in the day.

Best,

Sunny

Re: Sum prev date. Ignore holidays and weekends

Thu, 23 Apr 2015 13:24:55 GMT

Sounds good!

Actually it is the very same query as last time.

In order to calculate some stuff for "todays" date I need some values for the "previous open date".

Re: Sum prev date. Ignore holidays and weekends

Fri, 24 Apr 2015 08:07:01 GMT

Anyone?

Re: Sum prev date. Ignore holidays and weekends

Tue, 28 Apr 2015 11:08:06 GMT

Hi Sunindia!

Did you have time to look into the above yet?

I think we I could use the solution u came up with before.

However Im trying to implement it on this data with no luck.

I think it would be a done pretty fast if you had a look

Best regards,

Olle

Re: Sum prev date. Ignore holidays and weekends

sunny_talwar — Tue, 28 Apr 2015 11:09:45 GMT

Hahahaha, I will definitely spend some time on this today. I will have to revise what I did before and then I might be able to help. I will be leaving for work soon, so will look at this at work.

Best,

Sunny

Re: Sum prev date. Ignore holidays and weekends

Tue, 28 Apr 2015 13:36:24 GMT

Great, really apopreciate it mate!

//O

Re: Sum prev date. Ignore holidays and weekends

preminqlik — Tue, 28 Apr 2015 14:08:46 GMT

hi use the below expression for "Previous Total "

=if(rowno()=1,$(=sum({<Date={"$(=Date(min(Date)-1,'DD/MM/YYYY'))"}>}Cat1+Cat2+Cat3)),Above(sum(Cat1+Cat2+Cat3)))

find attachment ,

*Conditions apply - the result is for only one dimension not for multiple dimensions

Re: Sum prev date. Ignore holidays and weekends

Tue, 28 Apr 2015 14:18:12 GMT

Hi Prem,

thanks for your answer.

Problem is, for the dates with Today Total <>0 I always need a Previous Total <>0.

In other words, for instance if it's a monday, then previous total for this date should be fridays total.

However, it gets even more complicated when there is sometimes a weekend plus a few holidays inbetween the dates.

For instance, if thur and fri is a holiday, then next monday value for previous total should be the sum from wednesday the week before.

Thanks in advance,

Olle

Re: Sum prev date. Ignore holidays and weekends

sunny_talwar — Mon, 04 May 2015 14:50:59 GMT

Nobody has looked at it still? Would you be able to send me the file from our very last post and explain me what exactly you are trying to do differently this time in an email? I will send a private message to give you my id.

Best,

Sunny