8 Replies Latest reply: Mar 23, 2018 3:20 AM by Adam Hughes RSS

    Set Analysis/If statement date range query

    Adam Hughes

      Hello,

       

      I have the below data as an example and I am struggling to get correct formula for sales before and after "Date Change Made" per project and also count of number of weeks before and after "Date Change Made", regardless of whether there is a transaction in a week or not.


      [ProjectData]:

      Project Name, SKU     , Date Change Made

      A                    , 123      , 28/02/2018

      A                    , 456      , 28/02/2018

      A                    , 789      , 28/02/2018

      B                    , 123      , 01/02/2018

      B                    , 101      , 01/02/2018

      B                    , 102      , 01/02/2018


      [SalesData]:

      Invoice No, Invoice Date   , SKU  , Sales Total

      001,            01/01/2018     ,123     ,100

      002,            01/02/2018     ,456     ,100

      003,            01/03/2018     ,789     ,100

      004,            01/01/2018     ,123     ,100

      005,            01/02/2018     ,101     ,100

      006,            01/03/2018     ,102     ,100

      007,            01/01/2018     ,789     ,100


      [Calendar]:

      Invoice Date, YearWeek

      01/01/2018  , 2018-01

      01/02/2018  , 2018-05

      01/03/2018  , 2018-09


      I want to sum sales per project which is fine.

      I then want to sum sales per project where the invoice is within the 6 months prior to "Date Change Made" per project.

      The same for sum sales where invoice is after "Date Change Made" per project.


      For sales before and after i have something like this:

      Sum(if([Sales Invoice Date]<[Date change in front of customer] and [Is Sales Account]='Yes',[Sales Total (GBP)]))

      Sum(if([Sales Invoice Date]>=[Date change in front of customer] and [Is Sales Account]='Yes',[Sales Total (GBP)]))


      Can this be done in set analysis instead when there are different "Date Change Made" values?


      For the count of weeks I have this:

      count(distinct if([Sales Invoice Date]>=Date(addmonths([Date change in front of customer],-6)) and [Sales Invoice Date]<[Date change in front of customer] ,[Sales Invoice Date.Calendar.YearWeek]))


      But I need to count all YearWeek in the past 6 months and not just those that have a transaction in them.


      Any help is appreciated.

      Adam

        • Re: Set Analysis/If statement date range query
          Sunny Talwar

          Would you be able to share the expected output (numeric output) based on the sample you have provided?

            • Re: Set Analysis/If statement date range query
              Adam Hughes

              Hello,

               

              Output for the example data there would be:

               

              Project      /   Date of Change / Sales Before   /  # weeks (6 months prior to date change) /   Sales After  /   # Weeks (up to today)

              Project A   /   28/02/2018     /      300               /    27            /  100              / 4

              Project B   /   01/02/2018    /       200               /    27            /  200              / 8

               

              The calendar does contain missing dates and therefore missing weeks which need counting.

              There are 7 invoices total to £700 but £800 will appear in the results as sku 123 can appear in both project results.

               

              Thanks

            • Re: Set Analysis/If statement date range query
              Digvijay Singh

              Use something like this when Project Name is your dimension -

              Sum({<[Invoice Date]={">$(=Max(Aggr(AddMonths([Date Change Made],-6),[Project Name])))"}>}[Sales Total])

              One obervation - You have common SKU '123' for both project A and B, so I think one invoice row for SKU 123 will be double counted for Project A and B.

                • Re: Set Analysis/If statement date range query
                  Adam Hughes

                  Hi,

                   

                  That doesn't quite give the correct answer.

                  Take this real data as an example.

                   

                  For project name = 'no_description_SKUs_20180227' total sales is £160.09. The "Date Change Made" for this project is 28/02/2018.

                  So counting sales in 12 months prior to today should add to £160.09 using the invoice data on the right.

                  However it is missing the transaction on 10/05/2017 as it is not calculating the "Date Change Made" correctly. Another project has a "Date Change Made" of 29/05/2018 so it must be using this and not 28/02/2018

                   

                   

                   

                   

                   

                  pim.PNGpim2.PNG

                  Seems to be taking the max date of all projects and not per project.

                   

                  Thanks