13 Replies Latest reply on Apr 16, 2018 2:19 AM by Rajeev Pandey

# WorkoutWednesday 13 - Facing problems with LOD

Hi Team,

I was trying to solve the WW 13 but I am struck when I am calculating Average Sales per order. It working fine for the white portion but it's not working for other cells.

I know this can be easily done when we duplicate the database but I haven't duplicate the database and used the techniques which (Alexander Mou) taught us via densification.

WW Requirements:

I have created multiple formulas for testing the average Sales per order but it's not working at all.

When I am using this formula, I am ending up with like this.

Average sales per order should appear for each combination(Cell). I  tried a lot but don't know what I am missing here.

Alexander Mou

Simon Runc

• ###### 1. Re: WorkoutWednesday 13 - Facing problems with LOD

Hi Rajeev,

I have used these formulas. See if this can help.

~Tushar

• ###### 2. Re: WorkoutWednesday 13 - Facing problems with LOD

Thanks Tushar but the above formula will not work in this case. if you have noticed, my formula is also giving the same result/

Tre crux of the problem is to get the Average sales per order for each combination which I am not able to identify.

In your case, you duplicated the database which makes it easy to calculate.

• ###### 3. Re: WorkoutWednesday 13 - Facing problems with LOD

Hi Ranjeev

I had a look at your workbook.  I'm not familiar with the densification method you're using.  It may be that this is a drawback of not duplicating the data.  Have you got a link to the article/technique you're referring to?

Donna

• ###### 4. Re: WorkoutWednesday 13 - Facing problems with LOD

hi Ranjeev,

So I've not looked at this WoW, so not familar with the problem. I'll take a look tomorrow and see if I can see anything (I suspect it's to do with the number of extra occurrences the densification creates, but need to see how the LoDs work with this!)

Donna,

I think he's used the opposing blue pills and a table calculated method of densification (which is it's own murky, undocumented, world!!)

• ###### 5. Re: WorkoutWednesday 13 - Facing problems with LOD

Thanks Simon - Ranjeev did ping me this link via Twitter which has the details of the methodology used: Vizible Difference: Counting Customers Who Bought Both A and B via Data Densification .

I'm going to have a look to see if I can get my head round it, given I have a bit more understanding of where the numbers came from (there were averages of averages which bamboozled me for a while).  Making no promises I'll figure it out tho :-)

Donna

• ###### 6. Re: WorkoutWednesday 13 - Facing problems with LOD

OK - so I spent my lunch break having a look into this and have to say it's beyond me.... it might well just be a limitation of this method...

Simon - for info, there are a couple of other users who have posted a solution where they didn't have duplicated datasets, BUT they actually all applied an element of data manipulation to reshape the data & produce the calcs outside of Tableau either in SQL or via Alteryx first (which is slightly going against the purpose of #WW).

• ###### 7. Re: WorkoutWednesday 13 - Facing problems with LOD

Thanks Donna for all the suggestion. I tried a lot but couldn't figure it out and i don't have a solid reason to convince myself this cant be done. I want to understand what  I am missing here.

Simon Runc : Thanks for input Simon sir. When We are able to capture the number of partition(unique order id) based on Size, why can't we show the average sales per order for each box? Is this a limitation of LOD?

• ###### 8. Re: WorkoutWednesday 13 - Facing problems with LOD

hi Rajeev,

...Now I know why I don't play #WoW

So I think I've managed to solve this using Table Calculations only. The problem comes with this method of densification, where the various interacting table calculations can cause (or seem to) create further densification (or densification in different, and un-expected, directions).

If you look at the attached I've created 'SR' Tabs. Showing how I broke the problems down.

'Order Counts - SR' is (as you did) just bringing back the Order Counts for each combination.

'Order SUMs - SC - SR' - I have 2 Table Calculations; The first is just the Sales for Each Order (as a TC)

[SR - Sales by Order SC]

WINDOW_SUM(SUM([Sales]))

and this is set up like so

I then nest this in another one

[SR - Sales Total SC]

WINDOW_SUM([SR - Sales by Order SC])

and the extra nesting is set up like

Finally I bring these together to get the Average

[SR - Average Orders SC]

[SR - Sales Total SC]

/

[SR - Order Counts]

and do the whole index()=1 filter to only bring back a single mark.

As (I think) you need 2 sets of calcs, one for the SubCat and one for SubCat Copy, I've then recreated the calculations again (for SCD) but this time the first calculation [SR - Sales by Order SCD] is run over Sub Cat (Copy)

The 'SR - Final' brings these all together....and you can see in the ToolTip we have all the correct values.

I'll leave the fun part of adding some logic so the right version shows against the right level (don't want to hog all the fun).

I've only had a quick play, so don't fully understand exactly how it all works (there was a bit of experimenting ), but hope this helps and you can complete the challenge (...I'm off for a lie down, as this really made my brain hurt!)

3 of 3 people found this helpful
• ###### 9. Re: WorkoutWednesday 13 - Facing problems with LOD

Well done Simon - impressive!

PS - not all #WoW are that complex, and in this case the complexity was due to the densification technique being used...

• ###### 10. Re: WorkoutWednesday 13 - Facing problems with LOD

Thanks Donna. I'll admit there was a bit of "pressing and guessing"!!, but..."all's fair in love and Tableau"

• ###### 11. Re: WorkoutWednesday 13 - Facing problems with LOD

Here is my solution

Not happy about it yet because it is slow. The reason behind is I used 2-D table calculations which seem way slower than nested 1-D table calcs.

Will look into Simon Runc's solution which seems more efficient.

• ###### 12. Re: WorkoutWednesday 13 - Facing problems with LOD

Rajeev Pandey - I meant to say this the other day.  Just a point worth noting/highlighting on these solutions and your original query... the solutions don't used LoDs, but table calculations instead.  I imagine this is due to the densification technique, which is 'faking' data points, so there is no 'level of detail' to create a value against.

Donna

• ###### 13. Re: WorkoutWednesday 13 - Facing problems with LOD

Yes Donna.You are absolutely right.

I was in a wrong track and I'd don't notice what mistake I did.Afrer going through your comments and Simon's solution I realised my mistake.

But I must admit,I learnt a lot during this process and I am also happy that we were able to finish what we started.