Weekday Sales

[Anonymous]

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.

[Anonymous]

Hi Luis,

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

Thanks for your continued support.

[luismadriz · Accepted solution]

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

[Anonymous]

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.

[luismadriz]

Great to hear Vivek!

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

Cheers,

Luis

[Anonymous]

Hi Luis,

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

[luismadriz]

Hi Vivek,

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

Cheers,

Luis

[Anonymous]

Hi Luis,

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

Regards,

Vivek Ranawat