topic Re: Weekday Sales in QlikView

Weekday Sales

Anonymous — Tue, 14 Nov 2017 03:56:08 GMT

Hello All,

The problem what am facing is to report Last day sales on Monday. For rest of the days to report sales for the previous day is working fine.

I have use the formula "=IF((Today()- [Trans Date])<=1,'OK')" and i have untick box Include Null Value. This means if anything more than 1 day will be blank in the table.

The only problem is to report Sales on Monday where the previous day is Sunday. The solution what am looking for now is to get the Sales Table for the Friday. So on 20th Nov table should show data of 17th November that is Friday.

Transaction Dates	Days
11/14/2017	Tuesday
11/15/2017	Wednesday
11/16/2017	Thursday
11/17/2017	Friday
11/20/2017	Monday
11/21/2017	Tuesday
11/22/2017	Wednesday

I cannot select the Max date option, the reason is that if there are No Sales for the previous day still table will have data of Max Date Sales. The final requirement is Table should be blank if there are No Sales for the Previous date.

Re: Weekday Sales

luismadriz — Tue, 14 Nov 2017 05:14:49 GMT

Hi Vivek,

Maybe you want to complement your IF statement with a test of the WeekDay?

Not sure if you're trying to do this in a visualisation or in a load script,

Cheers

Luis

Re: Weekday Sales

luismadriz — Tue, 14 Nov 2017 05:25:08 GMT

Also, the function LastWorkDate(start_date, no_of_workdays {, holiday}) could be handy for you in case you need to incorporate holidays and not just weekends.

Regards,

Luis

Re: Weekday Sales

luismadriz — Tue, 21 Nov 2017 07:21:43 GMT

Hi,

Just wondering to know how you went about this one,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

Re: Weekday Sales

Anonymous — Tue, 21 Nov 2017 07:39:22 GMT

Hi Luis,

Still, i haven't found any perfect solution to my problem.

I tried the below IF Statement, the only problem is the previous Fridays sales also get opened.

=if(WeekDay(([Trans Date]))='Fri','OK',(IF((Today()- [Trans Date])<=1,'OK')))

Hope get this resolved at earliest.

Re: Weekday Sales

luismadriz — Wed, 22 Nov 2017 01:20:27 GMT

Hi,

As you mentioned that your original IF was working except for Mondays, then just confirm if it's Monday and make the adjustment ...

Please try with this: =if(Today()-[Trans Date]-IF(Num(WeekDay(Today()))=1,2,0)<=1,'OK')

Cheers,

Luis

Re: Weekday Sales

Anonymous — Wed, 22 Nov 2017 01:46:40 GMT

Hi Luis,

In your IF statement is 0 has been defined as Monday?

Re: Weekday Sales

luismadriz — Wed, 22 Nov 2017 02:22:00 GMT

Hi Vivek,

Did you try it?

If it's not Monday then it's your original If statement,. If it's Monday then I reduced it by 2 to include the Friday as the last worked day

Re: Weekday Sales

Anonymous — Wed, 22 Nov 2017 03:25:31 GMT

Hi Luis,

Thanks for your continuous support. But the problem is not yet resolved.

For Today report Sales figures (Only should reflect yesterday- Tuesday Sales) but Monday sales figures also getting included.

Re: Weekday Sales

luismadriz — Wed, 22 Nov 2017 03:45:14 GMT

Hi,

Can you please try this one?

=if((Today()-[Trans Date]-IF(Num(WeekDay(Today(),0))=1,2,0))<=1,'OK')

Please let me know,

Cheers,

Luis

Re: Weekday Sales

Anonymous — Fri, 24 Nov 2017 01:50:18 GMT

Hi Luis,

I have tried using the last formula. But still, the result is same.

Thanks for your continued support.

Re: Weekday Sales

luismadriz — Fri, 24 Nov 2017 03:41:00 GMT

Hi Vivek,

I had an error thinking that Monday was 1 when in reality is 0 when I use the second parameter of Weekday as 0. Also as my data had sales today (24/Nov) I decided to make sure that it'll only show the day before or Friday if today was Monday, in so for that I'm using Floor and =1 instead of <=1

So this worked for me, please see screenshot below. The second table gets automatically restricted to yesterday (or Friday if it is Monday).

The if changed to this:

=IF(Floor(Today())-Floor([Trans Date])-IF(Num(WeekDay(Today(),0))=0,2,0)=1,'OK')

And if you use it with a Measure as a Set Analysis it could look like this:

Sum({<[Trans Date]={"=Floor(Today())-Floor([Trans Date])-IF(Num(WeekDay(Today(),0))=0,2,0)=1"}>} SalesAmount)

I hope this helps,

Cheers,

Luis

Re: Weekday Sales

Anonymous — Mon, 27 Nov 2017 01:25:06 GMT

Hi Luis,

Very Good Morning. The formula what you have provided in the last threads of our discussion works perfectly.

=IF(Floor(Today())-Floor([Trans Date])-IF(Num(WeekDay(Today(),0))=0,2,0)=1,'OK')

Thanks Once again.

Re: Weekday Sales

luismadriz — Mon, 27 Nov 2017 01:31:13 GMT

Great to hear Vivek!

When you finish testing, please close the thread by marking the applicable post as Correct.

Cheers,

Luis

Re: Weekday Sales

Anonymous — Mon, 27 Nov 2017 01:35:15 GMT

Hi Luis,

I marked the activity as Helpful. Did i do the correct way to close the thread?

Re: Weekday Sales

luismadriz — Mon, 27 Nov 2017 01:40:36 GMT

Hi Vivek,

On the post that you find correct, see if you can find this and click on it:

Cheers,

Luis

Re: Weekday Sales

Anonymous — Mon, 27 Nov 2017 01:43:34 GMT

Hi Luis,

Thanks for the screenshot. I have marked the answer as correct.

Regards,

Vivek Ranawat