9 Replies Latest reply on May 4, 2018 7:57 AM by erik.furlanis

# LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

Hi all,

I am trying to achieve a logically simple calculation, which seems though hard to be rendered in Tableau.

I find it hard to explain it with words, so I'll just kick off with an example, sales of four products by week

Product
Week1Week2Week3
A201030
B10712
C5127
D564

What I want to do essentially is to flag products which do sell more than 10 units a week and subsequently I want to filter in only products that sell more than 10 units a week in at least 2 weeks.

Therefore, only product A and B would be filtered in.

I then want to show in a chart the total sales of this sub-selection of products, i.e.

Week1Week2Week3
total sales30 (20+10)17 (10+7)42 (30+12)

I find it hard to achieve it in Tableau because I essentially first need to count the number of weeks that a product is above 10 units and check if it happens for more than 2 weeks. To achieve this I need to essentially lose the Week dimension and use an Include LOD.

But then I need to bring back the week and lose the product dimension, and that is when things are getting hairy; I have used nested LODs without too much luck.

I achieved to filter in the products worth of consideration with the following:

IF

SUM({Include [WEEK] : (IF SUM([SALES]) > 10 THEN 1 ELSE 0 END)}) >= 2

THEN

1 ELSE 0

END

Does anyone have any suggestion?

Also to be kept in mind, I would like to make the two limits (how many units per week per product and how many weeks the minimum sales is met) dynamic by means of a parameter, but I expect this can be swiftly implemented with parameters once I get this calculation right.

Thanks!

• ###### 1. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

Hi, Erik

Below is steps:

2nd, create a calculation as filter

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

hi Erik,

I think this formula

{FIXED [Product]: COUNTD(IIF([Sales] >= 10,[Week],NULL))}>=2

will create the Product Dimension you want (and you can easily swtich out the 10 for a parameter). As a FIXED LoD it is a real row level dimension.

Hope that does the trick

• ###### 3. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

...I'd assumed the data was already "page down", but if not follow ZZ's pivoting step first!

1 of 1 people found this helpful
• ###### 4. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

Thank you for the inputs, really, really appreciated!

Unfortunately we have a slightly older version of Tableau, so I cannot open neither of your two workbooks.

Zhouyi, I could replicate your solution though thanks to your screenshot and it seems to be working, fantastic!!

{ FIXED [Product],[Pivot Field Names]:SUM(IF [Pivot Field Values] >=10

to

{ FIXED [Product],[Pivot Field Names]:(IF SUM([Pivot Field Values]) >=10

I think this is due to the fact I have day line details in my dataset, hence I need the SUM within the IF rather than outside of.

I will double check that it really works as intended and mark it as answered if it is so.

Let me say though, I have been crushing my head on this since yesterday afternoon, now I have to deal with a case of low Tableau self-esteem!

• ###### 5. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

Indeed dataset was already at line detail, so I skipped the pivoting of it!

• ###### 6. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

Hi, Erik

No worries. take your time. I think the reason why you need the if statement outside the sum is because your sales is already an aggregation in your real case? and this needs a bit of modification of the calculation since your change may leads to syntax error. Will wait for your question

ZZ

• ###### 7. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

hi Erik,

...now I have to deal with a case of low Tableau self-esteem!

...no one is born with this knowledge (and LoDs are a fairly advanced concept, especially when we are nesting them). I learned all this stuff by asking questions on the Community.

If your data is by day, you can make this tweak (nesting the SUM of Sales as another LoD at the level you want it calculated)

{FIXED [Product]: COUNTD(IIF({FIXED [Product], [Week]: SUM([Sales])} >= 10,[Week],NULL))}>=2

but this is then (almost) exactly the same solution as ZZ provided.

1 of 1 people found this helpful
• ###### 8. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

• ###### 9. Re: LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

hey Zhouyi

thank you again immensely for your help!

everything indeed does work as expected.

To elaborate on my slight modification: my dataset contains data at the level of product and sales per day.

I want to check whether the aggregated weekly sales meet a certain threshold, hence I think having a SUM(Pivot Field Values) >= 10 works in my situation as opposed to having the SUM (IF (Pivot Field Values) >=10 [..]; considering the granularity of my dataset, with your suggestion I think it checks whether the daily sales are above 10 or not.

Thank you again really!