7 Replies Latest reply on Oct 20, 2017 2:57 AM by aqua.lover

    Completing missing data

    aqua.lover

      Hi everyone,

      I would like to create a graph showing the evolution of prices and sales throughout the months.

      However, I do not have the prices for each month but just the times when the prices changed.

       

      What I have is something which looks like this:

       

      YearMonthShopSalesPricePrevious Price
      20171Shop110$

      Null

      Null

      20172Shop15$NullNull
      20173Shop19$1$0.8$
      20174Shop120$NullNull
      20175Shop112$1.2$1$
      20176Shop15$NullNull

       

      What I would like to do is to render the Price 'continous' by creating a calculated field 'Price month' which would give me this:

      YearMonthShopSalesPricePrevious pricePrice month
      20171Shop110$

      Null

      Null

      0.8$
      20172Shop15$NullNull0.8$
      20173Shop19$1$0.8$1$
      20174Shop120$NullNull1$
      20175Shop112$1.2$1$1.2$
      20176Shop15$NullNull1.2$

       

      The Price month can be calculated using the Previous price (e.g. the price that was there before the price change) and the new Price for instance.

      However I'm not sure how to do this...

      Any idea?

       

      Thanks a lot!

      Cheers

        • 1. Re: Completing missing data
          Paul Cawford

          Hi,

           

          This is possible through some table calculations as follows (please note I've created a 'real date' rather than month / year to ensure sorting works correctly.

           

          Using the following steps:

           

          [VAR_Date] - THis just converts the month / year to a real date for sorting purposes.  Not required if your data source already has this.

          DATE(STR([Year])+"-"+STR([Month])+"-01")

           

           

          [V1_Previous Price First Date] - This is necessary to work out the first date that has a 'previous price' for pulling back to the earliest date.  This uses {fixed :} to ignore any row context and get the minimum of all values.  Please note that i

          {FIXED : MIN(IF NOT ISNULL([Previous Price]) THEN [VAR_Date] END)}

           

           

          [V2_First Previous Price] - This simply gets the first instance of previous price using the original V1 variable.  This again uses {fixed :} to ignore the row context.  THis could be integrated into a singe variable if desired.

          {FIXED : (MIN(IF [VAR_Date] = [V1_Previous Price First Date] THEN [Previous Price] END))}

           

           

          [Price Month] - This variable is split into two distinct parts.  The first part around getting the pervious price simply says if the current price is null then return the price from the last line that had a value.  This will inly work once a value has existed i.e. lines 1 and 2 in your data with this alone would be null.  The second part then returns that first ever [previous price] if no price has existed i.e. the result is still null.  Once pulled into the data set the table calculation must be set to Compute Using --> Table (down)

          IFNULL(

           

          //Get the previous price if the current is null
          IF ISNULL(LOOKUP(ATTR([Price]),0)) THEN PREVIOUS_VALUE( ATTR([Price])) ELSE ATTR([Price]) END,

           

          //Although if null return the first ever price
          MIN([V2_First Previous Price])
          )

           

          I've attached a working example.  Hopefully this helps but I do realise I've skimmed over the technical parts relatively quickly.  Also you can use SUM([Price]) instead of ATTR([Price]) if you data source requires.

           

          Regards,

          Paul

          • 2. Re: Completing missing data
            Paul Cawford

            I've just noticed you wanted a graph - please see attached example about how to achieve this:

             

            • 3. Re: Completing missing data
              aqua.lover

              Dear Paul

              Thank you very much for your quick answer.

              I'm trying to implement this now and it is working fine in the case I have one product and one shop.

              However, in my case the data I have is a bit more complex: I have multiple shops with multiple products and those shops are linked to multiple retailers. I noticed that your formula really takes the smallest price for all shops/products/retailers.

              How can I make the price adjust based on this?

              Thanks!

              • 4. Re: Completing missing data
                aqua.lover

                To complete what I said previously, the data i have is more like this:

                 

                YearMonthRetailer NameAccountBrand FamilySKU GroupSKUSalesPricePrevious pricePrice continuous
                20171Retailer 1Account 1ChocolateDark ChocolateChocolate 110$NullNull0.5$
                20171Retailer 2Account 1SodaLight SodaSoda 15$1$0.5$1$
                20172Retailer 1Account 1ChocolateDark ChocolateChocolate 112$1.5$1$1.5$
                20173Retailer 1Account 1ChocolateMilk chocolateChocolate 21$NullNull1$
                20173Retailer 1Account 1ChocolateDark ChocolateChocolate 112$1.5$1$1.5$
                20173Retailer 2Account 1SodaLight SodaSoda 16$NullNull1$
                20174Retailer 1Account 1ChocolateMilk chocolateChocolate 25$1.2$1$1.2$

                 

                There are other columns but I assume that the logic extends to more columns.

                 

                Sorry for not providing the complete information.

                • 5. Re: Completing missing data
                  Paul Cawford

                  No problem at all.

                   

                  There is a solution although it gets fiddly due to the Level of Detail (LOD) calculations involved.  My solution would be:

                   

                  in the [V1] and [V2] variables before in the {fixed :} section you need to add the required variables in before the : to set them as levels of detail e.g. V1 including [Shop] and [Product] as a detail would be:

                   

                  {FIXED [Shop],[Product] : MIN(IF NOT ISNULL([Previous Price]) THEN [VAR_Date] END)}

                   

                  Repeat the same for V2. If you also wanted retailer then include this too separated by a comma.

                   

                  The [Price Month] variable remains unchanged but you need to change the table calculation by right clicking and selecting "Edit Table Calculation..."

                   

                  In the options select "Specific Dimensions" and then uncheck every variable that you've included in the above {fixed} e.g.

                   

                  This tells tableau to look for the previous value in the same [Product] and [Shop].

                   

                  Now the major caveat to this is that the chart / table must include [Shop] and [Product] to work correctly.  I feel that I may have led you down a potentially difficult route although I'll have another think about this to see if there are any alternatives.  Although off the top of my head I think it would be much easier for everyone if there was any possible way to fix the source data to add the price to every line.  That 'may' be possible through some custom SQL although it depends on your data source as to whether this is an option.

                   

                  As always, example attached.

                   

                  Regards,

                  Paul

                  • 6. Re: Completing missing data
                    aqua.lover

                    Dear Paul

                    Thanks a lot. I started fixing the variables I need but I'm afraid this is going to get quite complicated. Let me give it a try though!

                    For the datasource fix, we thought about it. The thing is that the amount of data is so big that we decided to not put all the prices so as to reduce the size of the data...

                    • 7. Re: Completing missing data
                      aqua.lover

                      Hi Paul

                      I tried implementing your solution. The problem gets quite complicated when I have for multiple products, multiple change prices in one given month. Then the 'Price Month shows me *.

                      Not really sure what to do...

                      In the end I would like to get the max price at each level of detail.

                      I think... Those notions are not completely clear to me.

                      Cheers