-
1. Re: Blended Data LOD Calculations
Simon RuncDec 19, 2017 5:07 AM (in response to Stephen Bava)
2 of 2 people found this helpfulhi Stephen,
So this is one of the drawbacks with Blending....COUNTDs! I'm surprised that blending is so much more efficient than Joining (maybe look at the indexing in your DB)...but on to the problem at hand!
So Blended fields always come in as an aggregate, and COUNTD need the COUNTD level in the Viz in order for it to work. However we can use a clever trick to have Customer in the VizLoD, but only return a single mark for each status.
First I created this formula
[Customer COUNTD]
WINDOW_SUM(COUNTD([Customer Name]))
and ran this at the customer level. However your dimension (Review Complete/Needs Review) is an aggregate calculation (it has to be as you are blending), so a Table Calculation (by default) won't use this (aggregated) dimension to partition the data. However this is a (little known) option to tell Tableau to include it
Untick "Ignore in Table Calculations"
So now we have the right counts for each status, but we have this value (mark) repeated for every customer...so one last little trick; We add the following to our calculation
IF FIRST() = 0 THEN
WINDOW_SUM(COUNTD([Customer Name]))
END
This has the effect of only returning a value for the 1st mark (as there is no else all other marks equate to NULL, and so don't get plotted)
Finally is the %age, which I did like this
IF FIRST() = 0 THEN
WINDOW_SUM(COUNTD([Customer Name]))/SUM({COUNTD([Customer Name])})
END
and as I've used an LoD to get the number of all customer, I also had to add your segment filter to "context" so it would affect the FIXED LoD.
hope that helps and makes (some) sense
-
2. Re: Blended Data LOD Calculations
Norbert MaijoorDec 19, 2017 6:36 AM (in response to Simon Runc)
Hi Simon,
Always nice to get a "precious gift" during the holiday season.
Be ensured it will be stored in my "Treasure House".
Enjoy Christmas together with your loved ones
Regards,
Norbert
-
3. Re: Blended Data LOD Calculations
Simon RuncDec 19, 2017 6:48 AM (in response to Norbert Maijoor)
Thanks Norbert
I actually stole (and then re-gifted!) this one from Jonanthan!! (...who else!), but it is the season for sharing
Have a great Xmas too.
-
4. Re: Blended Data LOD Calculations
Norbert MaijoorDec 19, 2017 7:00 AM (in response to Simon Runc)
Hi Simon,
hihihi..You have even "stolen" the "business model" of our beloved Father Christmas...but most important it all ends up in my Treasure House where it will be treated with great care:))))
Regards,
Norbert
-
5. Re: Blended Data LOD Calculations
Stephen Bava Dec 19, 2017 7:22 AM (in response to Simon Runc)That is incredible! Works perfectly. Thanks for taking the time to answer this. You just solved a lot of time and future efforts.