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

# Completing missing data

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

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

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

• ###### 3. Re: Completing missing data

Dear Paul

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

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

 Year Month Retailer Name Account Brand Family SKU Group SKU Sales Price Previous price Price continuous 2017 1 Retailer 1 Account 1 Chocolate Dark Chocolate Chocolate 1 10\$ Null Null 0.5\$ 2017 1 Retailer 2 Account 1 Soda Light Soda Soda 1 5\$ 1\$ 0.5\$ 1\$ 2017 2 Retailer 1 Account 1 Chocolate Dark Chocolate Chocolate 1 12\$ 1.5\$ 1\$ 1.5\$ 2017 3 Retailer 1 Account 1 Chocolate Milk chocolate Chocolate 2 1\$ Null Null 1\$ 2017 3 Retailer 1 Account 1 Chocolate Dark Chocolate Chocolate 1 12\$ 1.5\$ 1\$ 1.5\$ 2017 3 Retailer 2 Account 1 Soda Light Soda Soda 1 6\$ Null Null 1\$ 2017 4 Retailer 1 Account 1 Chocolate Milk chocolate Chocolate 2 5\$ 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

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

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

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