Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
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.
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,
Hi Luis,
In your IF statement is 0 has been defined as Monday?
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
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.
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