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

    total sales for Sunday/ # of sundays

    Vishal D

      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
          Jim Dehner

          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
            Vishal D

            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
              Jim Dehner

              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
                Vishal D

                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
                  Jim Dehner

                  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
                    Vishal D

                    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
                      Vishal D

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

                        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
                          Jim Dehner

                          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
                            Vishal D

                            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
                              Jim Dehner

                              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
                                Vishal D

                                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
                                  Shinichiro Murakami

                                  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
                                    Vishal D

                                    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