Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vivekranawat
New Contributor II

Weekday Sales

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 DatesDays
11/14/2017Tuesday
11/15/2017Wednesday
11/16/2017Thursday
11/17/2017Friday
11/20/2017Monday
11/21/2017Tuesday
11/22/2017Wednesday

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.

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: Weekday Sales

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

Untitled.png

16 Replies
luismadriz
Valued Contributor

Re: Weekday Sales

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

luismadriz
Valued Contributor

Re: Weekday Sales

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

luismadriz
Valued Contributor

Re: Weekday Sales

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

vivekranawat
New Contributor II

Re: Weekday Sales

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.

luismadriz
Valued Contributor

Re: Weekday Sales

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

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,

vivekranawat
New Contributor II

Re: Weekday Sales

Hi Luis,

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

luismadriz
Valued Contributor

Re: Weekday Sales

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

vivekranawat
New Contributor II

Re: Weekday Sales

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.

luismadriz
Valued Contributor

Re: Weekday Sales

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

Community Browser