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.
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?
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!)
I think he's used the opposing blue pills and a table calculated method of densification (which is it's own murky, undocumented, world!!)
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 :-)
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).
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?
3 of 3 people found this helpful
...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]
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!)
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...
Thanks Donna. I'll admit there was a bit of "pressing and guessing"!!, but..."all's fair in love and Tableau"
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.
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.
Thanks again for all your support ,advice and feedback.Keep Guiding us with your valuable tableau skills
Sent from TypeApp<http://www.typeapp.com/r?b=12754>