1 2 Previous Next 18 Replies Latest reply on Jan 27, 2017 4:44 PM by Vishal D

# total sales for Sunday/ # of sundays

I have a [Sales Date Time] field and [Profit]

I have given that as 'range of date' filter for the user to choose start date & end date

I want [Sales] per week of day based on what dates user selected.

For e.g - What was my sales Avg for just 'Sunday' - so total profit for the sunday's withing the date range selected by user divided by count of 'Sundays'?

Similarly I want to do Avg per month trend for eg user select 1/1/2015 to 12/31/2016 so Total Jan sales divided by # of jan (2) and plot Jan to Dec line chart with avg numbers.

If this doesnt make sense I will try to out a rough twbx

• ###### 1. Re: total sales for Sunday/ # of sundays

Hi

See the attached T10.1 file

For the first part I set up a parameter to allow you change the day of the week :

Then using that parameter set up a filter"

Day Filter

if DATENAME('weekday',[Order Date] ) = [Enter Day of Week] then 1 else 0 end

And apply the filter to the filter table - now the viz will only include data for that specific day of the week - you can total or do what ever you need from there

I don't really understand what you are asking in the second question

Jim

2 of 2 people found this helpful
• ###### 2. Re: total sales for Sunday/ # of sundays

Jim, my only filter for user is to select a 'From Date' & 'To Date' then i need to calculate 'total volume' for that selected date range divided by the day of week (# of total Sundays in that selected date range).

See attached chart. Right now I am just doing sum of 'volume' by weekday but I want average ( total sum of volume for each weekday/ # of times that weekday occurred)

1 of 1 people found this helpful
• ###### 3. Re: total sales for Sunday/ # of sundays

Ok

I am opn V10.1.3 don't know you will be able ot open the attached:

I understand better - not certain this is everything you need but:

Here are the steps:

Create a field - Total time for average >>FLOAT(DATEDIFF( [Select Time Slice],Min([Transaction Date]),max([Transaction Date])))

Where the transaction date comes off your filter and the date part comes off your Select Time Slice

Next change the parameter values to:month, week, day, hour but continue to display them as you have

Next create a calculated field Average over time period>> sum([Volume (Bbls)])/[total time for average]

Now in the viz you don't need to do all the month, day year etc calculation

This is what I got

Let me know

Jim

1 of 1 people found this helpful
• ###### 4. Re: total sales for Sunday/ # of sundays

Jim the math doesn't seem to add up.

For e.g Lane 2 - total volume for Sunday is 96535 from 1/1/2014 to 1/25/2017 and total number of sundays between this date range is 160 (52+52+52+1)

So 96535/160 = 603.34 but with your logic I am getting 1583

Using Jim's approach -

1 of 1 people found this helpful
• ###### 5. Re: total sales for Sunday/ # of sundays

Ok so the time slice is really not relevant - you are hot interested in the hours, days, or months only the number of weeks - e.g, the number of sundays -

Then the formula for the number of weeks is as shown

Then you base your average on that

Jim

1 of 1 people found this helpful
• ###### 6. Re: total sales for Sunday/ # of sundays

Jim - For Lane 2 - total number of sundays between this date range(from 1/1/2014 to 1/25/2017 ) is 160 (52+52+52+1)

But below formula is giving 61 count.

What value are you getting?

I think still something is not right.

1 of 1 people found this helpful
• ###### 7. Re: total sales for Sunday/ # of sundays

1 of 1 people found this helpful
• ###### 8. Re: total sales for Sunday/ # of sundays

The problem is the MIN & MAX date is not taking into consideration what the user selected but its going by the underlying data MIN & MAX

I want to use the dates what the user selected in my calculation

1 of 1 people found this helpful
• ###### 9. Re: total sales for Sunday/ # of sundays

In the data set I have the min date is 11/14/15 -

The calculation is picking up the min date within the range that also meets the lane and day of week criteria and has a value

Jim

1 of 1 people found this helpful
• ###### 10. Re: total sales for Sunday/ # of sundays

ok even if you consider Start Date as 11/14/15 and End Date as 1/16/2017 the number of Sundays should be around 113  (2014 =7  & 2015 = 52, 2016 = 52, 2017 = 2)

I dont see a number close to 113 in you screenshot

1 of 1 people found this helpful
• ###### 11. Re: total sales for Sunday/ # of sundays

Ok the actual max number is 61 - you counted the start date is 11/14/15

that said the calculation I sent you counts the number of days between the first occurrence and the last occurrence when there was a value on the specified day of the week. It Counts all the weeks in between even if there were values - I think you only want to count the week where there were values.

I don't know how to do that - maybe someone else does

Jim

1 of 1 people found this helpful
• ###### 12. Re: total sales for Sunday/ # of sundays

I just want the count of total number of Sundays (days of week) between 2 dates selected by user.

If you consider Start Date as 11/14/15 and End Date as 1/16/2017 the number of Sundays should be around 113  (2014 =7  & 2015 = 52, 2016 = 52, 2017 = 2)

1 of 1 people found this helpful
• ###### 13. Re: total sales for Sunday/ # of sundays

Hi, Vishal

I have not gone thru the conversation, but only focus on the last request.

// Date range should be filtered as "Context Filter" for LOD to work

[Start Date]

{fixed:min([Date])}

[End Date]

{fixed:max([Date])}

[Last Monday before Start]

date(datetrunc('week',[Start Date],"Monday"))

[Next Saturday From End]

date(datetrunc('week',[End Date]+6,"Saturday"))

[Count Sunday]

floor(([Next Saturday From End]-[Last Monday before Start])/7)

Reference

EXCEL's "networkdays" s alternative

Thanks,

Shin

2 of 2 people found this helpful
• ###### 14. Re: total sales for Sunday/ # of sundays

Shinichiro Murakami

I used

// Date range should be filtered as "Context Filter" for LOD to work

[Start Date]

{fixed:min([Date])}

[End Date]

{fixed:max([Date])}

But my real requirement is to show Average [Volume (Bbls] per 'day of week' based on what dates user selected.

So for Sunday = total volume for Sunday/ count of total Sundays which has some volume record

Monday = total volume for Sunday/ count of total Monday which has some volume

Tuesday = total volume for Sunday/ count of total Tuesday which has some volume

Wednesday = total volume for Sunday/ count of total Wednesday which has some volume

Thursday = total volume for Sunday/ count of total Thursday which has some volume

Friday= total volume for Sunday/ count of total Friday which has some volume

Saturday= total volume for Sunday/ count of total Saturday which has some volume

How do I achieve this?

right now I just doing SUM(Volume (Bbls) and showing chart by day of week

Then I want to do same thing for months, days and hours

Attached twbx

1 of 1 people found this helpful
1 2 Previous Next