15 Replies Latest reply on Jan 6, 2016 4:49 PM by mark schukas

# calculating sales by day of week

(improvements, but still not a "home run"...)

i'm using (and works well - in my testing to date)

(Report Start/End are parameters and RevenueDate is a date dimension)

if (([Report End] >= [RevenueDate] AND [Report Start] <= [RevenueDate]))

then {include [Calcu_Weekday] : SUM([Revenue])}

ELSE

null

END

in addition I would like to do a calculated field that that sums Revenue by Day of Week (Calcu_Weekday = DATENAME('weekday',[RevenueDate])...?

so I modified the above to:

if ((([Baseline End] >= [RevenueDate] AND [Baseline Start] <= [RevenueDate])

and [DayOfWeek] = 'Sunday')) THEN {fixed [DayOfWeek] : AVG([Revenue])}

//etc.

but I get a different answer for Sunday's revenue...???

thank you.

• ###### 1. Re: calculating sales by day of week

Hey Mark,

From the looks of it...

if ((([Baseline End] >= [RevenueDate] AND [Baseline Start] <= [RevenueDate])

and [DayOfWeek] = 'Sunday')) THEN {fixed [DayOfWeek] : AVG([Revenue])}

The issue you are having is coming from the Logic being outside of the LoD Expression. What is happening is {fixed [DayOfWeek] : AVG([Revenue])} is returning ALL AVG([Revenue]) by each day of the week, regardless if the Days falls between you parameters.

What you need to do is add the logic inside the LoD Statement

{fixed [DayOfWeek] : AVG( IF [Baseline End] >= [RevenueDate] AND [Baseline Start] <= [RevenueDate] THEN [Revenue] END)  }

This way, only the values that fit within the range are Averaged, and returned at the level of DayOfWeek, and whatever you vizLoD is. I don't think you need to explicitly  write  out the Week Day Name, since you already account for the day of the week in the Dimension Declaration.

If this does't work for you, can you provide an example workbook, or perhaps a screenshot of your viz so I can see how things are setup.

Regards,

Rody

• ###### 2. Re: calculating sales by day of week

It is also worth mentioning that FIXED LoDs are computed before Regular filters, so you may be seeing issues there as well.

Regards,

Rody

• ###### 3. Re: calculating sales by day of week

thank you...

will try ASAP and update...

thank you.

• ###### 4. Re: calculating sales by day of week

thank you...

i'm trying to find the Average Sales per Day Of Week (i'm trying to duplicate this with your idea in the Super Store file):

//Calcu_NEW

{fixed [Day Of Week] : AVG( IF [Baseline End] >= [RevenueDate] AND [Baseline Start] <= [RevenueDate] THEN [Revenue] END )  }

will attach shortly.

• ###### 5. Re: calculating sales by day of week

attachement

that I get with the following (correct numbers on SuperStore file)

if (([Report End] >= [Ship Date] AND [Report Start] <= [Ship Date]))

then FLOAT([Sales])

ELSE

null

END

thank you.

• ###### 6. Re: calculating sales by day of week

I also tried: (error: "can't compare integer and string")

{fixed [Day Of Week] : AVG( IF ((([Baseline End] >= [RevenueDate]

AND [Baseline Start] <= [RevenueDate])) and [Day Of Week] = 'Sunday') THEN [Revenue] END )  }

???

• ###### 7. Re: calculating sales by day of week

Can you post the example workbook you are doing these on?

Regards,

Rody

• ###### 8. Re: calculating sales by day of week

here's my attachment (superstore)

• ###### 9. Re: calculating sales by day of week

Hey Mark,

Two things

1. The LoD Expression is Declaring the [Date] as the Level of Detail, when it needs to be the WeekDay Calc

2. Your are comparing a SUM([Sales]) to and AVG....

So you need to change it from SUM([Sales]) to AVG([Sales])

All this being said, why do you need the LoD Expression? If you intend to build a Table like this, you don't need it. All you need it to create your calc like so.

AVG( IF [Baseline End] >= [Date] AND [Baseline Start] <= [Date] THEN [Sales] END )

And you get the same result.....

Is there a particular reason you were trying to use an LoD?

Regards,

Rody

• ###### 10. Re: calculating sales by day of week

fyi - i'm trying to get the Average Sales/Revenue for the Day of Week by parameter ranges

//Calcu_DayOfWeekAvgSales - seems to working in the test.twbx

{fixed [Date] : AVG( IF [Baseline End] >= [Date] AND [Baseline Start] <= [Date] THEN [Sales] END )  }

if this is correct, the next problem is that I don't want to have the Day of Week calculated field on the Viz (at least showable to the user) - but I seem to need to get this get the correct totals by Day of Week..

//Calcu_DayOfWeek

DATENAME('weekday',[Date])

???

thank you.

• ###### 11. Re: calculating sales by day of week

Your range is only 1 day, so there is only 1 weekday, that is why you are getting the correct total.

What do you mean by you don't want to include WeekDay Calc in your viz? Can you elaborate more on what your end goal is?

Regards,

Rody

• ###### 12. Re: calculating sales by day of week

thank you.

"using a LOD..."

initially I didn't...and then working with the parameters I got the calc to work with the LOD...so I don't have good logic here, other then "it worked this way"

n.b., I need the past average by day of week from one set of parameters to use in a Business Rules calculation for the current parameters sales (a second date range)

--basically comparing the average of the old sales by day of week to current sales...

OK?

thank you.

• ###### 13. Re: calculating sales by day of week

So you want "Current Period" total Sales, compared to the "Previous Period Avg".

Where both Current Period, and Previous Period are controlled by Parameters?

For the Previous Period, you want to SUM Revenue each Day, and then do the AVG of those Days?

Regards,

Rody

• ###### 14. Re: calculating sales by day of week

thank you.

yes...(I believe...I will state business rules)

create a date range in Previous Rev. parameters

calculate average rev by day of week (Monday, Tuesday, etc.)

create a date range in the Current Rev. parameters (different parameter than above-user has to enter four total dates)

count the number of days of week (e.g., 9/1 to 9/9/15: 1 Monday,