Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2017-11-13 10:56 PM

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,977 Views
0 Likes
1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist
‎2017-11-23 10:41 PM

In response to Anonymous

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

View solution in original post

1,907 Views
16 Replies
luismadriz
Specialist
Specialist
‎2017-11-14 12:14 AM

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

1,381 Views
0 Likes
luismadriz
Specialist
Specialist
‎2017-11-14 12:25 AM

In response to luismadriz

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

1,381 Views
0 Likes
luismadriz
Specialist
Specialist
‎2017-11-21 02:21 AM

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

1,381 Views
0 Likes
Anonymous
Not applicable
‎2017-11-21 02:39 AM
Author

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.

1,381 Views
0 Likes
luismadriz
Specialist
Specialist
‎2017-11-21 08:20 PM

In response to Anonymous

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,

1,381 Views
0 Likes
Anonymous
Not applicable
‎2017-11-21 08:46 PM
Author

Hi Luis,

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

1,381 Views
0 Likes
luismadriz
Specialist
Specialist
‎2017-11-21 09:22 PM

In response to Anonymous

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

1,381 Views
0 Likes
Anonymous
Not applicable
‎2017-11-21 10:25 PM
Author

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.

1,381 Views
0 Likes
luismadriz
Specialist
Specialist
‎2017-11-21 10:45 PM

In response to Anonymous

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

1,381 Views
0 Likes