4 Replies Latest reply on Apr 18, 2016 12:51 PM by jagaddish reddy

# if its not a business day then add sales to next or previous day

Hi All,

Actually the requirement is 'if its Sunday then sales of sunday should add to monday and if its Saturday then its sales should added to Friday sales'. For this I have created weekdays sales calculations as " if DATEPART('weekday', [Order Date])=1 then [Sales] END " for weekday 1 similarly for remaining all weekdays i have created calculated fields.

Then I've created a calulation as "

if DATEPART('weekday', [Order Date])=1 then null

ELSEIF DATEPART('weekday', [Order Date])=2 then [weekday2_sales]+[weekday1_sales]

ELSEIF DATEPART('weekday', [Order Date])=3 then [weekday3_sales]

ELSEIF DATEPART('weekday', [Order Date])=4 then [weekday4_sales]

ELSEIF DATEPART('weekday', [Order Date])=5 then [weekday5_sales]

ELSEIF DATEPART('weekday', [Order Date])=6 then [weekday6_sales]+[weekday7_sales]

ELSEIF  DATEPART('weekday', [Order Date])=7 then null

END "

Please find the workbook in attachments and help me in this context

Thanks,

• ###### 1. Re: if its not a business day then add sales to next or previous day

I'm assuming the end goal of what you are trying to do looks something like this

If so, this is how I approached it

Step 1.

Create a calculated field to adjust for Sunday and Saturday

(IF DATEPART('weekday', [Order Date]) = 1

THEN DATEPART('weekday', [Order Date]) + 1

ELSEIF DATEPART('weekday', [Order Date]) = 7

THEN DATEPART('weekday', [Order Date]) -1

ELSE DATEPART('weekday', [Order Date])

END) -1

I subtract 1, so that we return 1 - 5, instead of 2 -6, and I'll explain why.

Step 2. Drag that calculated field onto the Columns Shelf, and Sales onto the Text Mark Card

Step 3. Format your Column to Show Weekday

Right click on the columns and select format

Go to Custom and Type

"Weekday" #

And then that's it!

The formatting trick just allows you to add a constant literal string to the Column Formatting, without you having to worry about converting it to a string. Plus this avoids having to write excess calcs, worry about measure names/values

Regards,

Rody

3 of 3 people found this helpful
• ###### 2. Re: if its not a business day then add sales to next or previous day

provide me your email id .

• ###### 4. Re: if its not a business day then add sales to next or previous day

Hi Rody,

Thanks for your immediate response. Iam on vacation so iam reverting you this late.