
1. Re: Help with calculating a group of data
Simon Runc Nov 17, 2016 9:42 AM (in response to Sam Bloodgood)1 of 1 people found this helpfulhi Sam,
Glad you are enjoying Tableau...it's a pretty awesome bit of kit! (and actually the best thing about it, IMHO, is the Community!!)
So just looking at your Discount Group calculation, and have a question...As you have made it an aggregate calculation (in that the test of which [Ship Qty] get's which discount group, is in a SUM wrapper), it means that the VizLoD (viz Level of Detail) determined how the calcuated field is evaluated (eg, if I remove Item from the columns)
I only have one group...the test is SUM([Ship Qty]) and as I no longer have any Item in the vizLoD this is assessed at the total level (25,387 which is >2500 so we only get the single assignment of 50%). I assume you don't want to have to keep Item in the Viz, and would like this calculation to be a "real" dimension, so you can slide the data up by this?
If that's the case can you let me know at what level you want the various tests assessed at? eg. If it's just at item level, then we'd have the same discount group for every occurance of that item, if it's by shipment, then an item could appear in different discount bands depending how many were ordered in that shipment...if that makes sense?
If you let me know this, we can use a FIXED LoD to create the discount group as a proper dimension, so is independant of the VizLoD...if the whole aggregate, VizLoD...etc. makes no sense, this might help Answer  Quora
this is an explanation of the idea of oncanvas and offcanvas calculation types.

2. Re: Help with calculating a group of data
Sam Bloodgood Nov 17, 2016 11:27 AM (in response to Simon Runc)1 of 1 people found this helpfulHI Simon,
I have tried several different ways to figure this out. I sent the one that got me closest...
To your question;
If that's the case can you let me know at what level you want the various tests assessed at? eg. If it's just at item level, then we'd have the same discount group for every occurance of that item,  YES
if it's by shipment, then an item could appear in different discount bands depending how many were ordered in that shipment...if that makes sense?  I got this part (but not a count of pn's in each discount band...
 I need to SUM the total shipped of each item, then based on the sum, put that sum into the "discount group." and then I would like to know how items are in each group.
Thanks again for working on this. I will be busy the rest of today, but will check back soon for any help...

3. Re: Help with calculating a group of data
Simon Runc Nov 17, 2016 11:41 AM (in response to Sam Bloodgood)Cool...so yes LoDs are one way to go...and especially here as we want to use the groups as a dimension (to slice the data by) without wanting Item in the view every time.
So first I created the following FIXED LoD
[Ship Qty by Hfitem  LoD]
{FIXED [Hfitem]: SUM([Ship Qty])}
So this creates (offcanvas) the SUM of Qty aggregated at the Hfitem level. So I can now use this to create the discount groups (I simplified your formula...as IFs exit after a condition is met, we don't need to test for > and <, as long as we have the ELSEIFs in the right order!)
[Discount Groups  SR  LoD]
IF [Ship Qty by Hfitem  LoD]=1 then "x1.3"
ELSEIF [Ship Qty by Hfitem  LoD]<=4 THEN "x1.2"
ELSEIF [Ship Qty by Hfitem  LoD]<=9 THEN "list"
ELSEIF [Ship Qty by Hfitem  LoD]<=24 THEN "20%"
ELSEIF [Ship Qty by Hfitem  LoD]<=49 THEN "30%"
ELSEIF [Ship Qty by Hfitem  LoD]<=99 THEN "40%"
ELSEIF [Ship Qty by Hfitem  LoD]<=249 THEN "43%"
ELSEIF [Ship Qty by Hfitem  LoD]<=499 THEN "45%"
ELSEIF [Ship Qty by Hfitem  LoD]<=999 THEN "48%"
ELSEIF [Ship Qty by Hfitem  LoD]<=2499 THEN "49%"
ELSE "50%"
END
Once I have this, I can use this as a real dimension at will....I've done a few example's
Hope that's what you wanted, and makes sense....let me know if not,

discount groups using LoD.twbx 693.9 KB


4. Re: Help with calculating a group of data
Sam Bloodgood Nov 18, 2016 8:49 AM (in response to Simon Runc)Hello again,
This got me much further along. Thanks!
The calculation "ship QTY LOD"
{FIXED[Hfitem]:SUM([SHIP_QTY])} is giving me the total pieces shipped alltime.
Is there a way to filter that qty to a specific period?

5. Re: Help with calculating a group of data
Simon Runc Nov 18, 2016 8:53 AM (in response to Sam Bloodgood)1 of 1 people found this helpfulhi Sam,
Yes...so any further cuts of the level at which this is Aggregated can just be added to the left side of the LoD. So to add date, so get a SUM([ship Qty]) for each Item/Date, say, would be
{FIXED[Hfitem], [Date]:SUM([SHIP_QTY])}
and you can keep adding any other further cuts you want.

6. Re: Help with calculating a group of data
Sam Bloodgood Nov 18, 2016 9:20 AM (in response to Simon Runc)1 of 1 people found this helpfulThanks Simon, I am getting errors trying to enter a date.
{FIXED[Hfitem],DATE():SUM([SHIP_QTY])}
ERROR  "only column names are permitted...
Sorry to bother...

7. Re: Help with calculating a group of data
Simon Runc Nov 18, 2016 9:26 AM (in response to Sam Bloodgood)1 of 1 people found this helpfulYes that DATE() is a function for returning something (say a string) as a date object...you'll need to use the field in your data which relates to date
so I think your is [Ivc Date]
So would be
{FIXED[Hfitem], [Ivc Date]:SUM([SHIP_QTY])}

8. Re: Help with calculating a group of data
Sam Bloodgood Nov 18, 2016 9:34 AM (in response to Simon Runc)1 of 1 people found this helpfulWhoa!! Powerful! I love it and Thank you so much for help and understanding.

9. Re: Help with calculating a group of data
Sam Bloodgood Nov 18, 2016 9:42 AM (in response to Sam Bloodgood)Simon, I am still getting strange results. I will continue to work on this and let you know how I get along...

10. Re: Help with calculating a group of data
Simon Runc Nov 18, 2016 9:43 AM (in response to Sam Bloodgood)Yes LoDs are awesome!! (such a clever creation from those clever bods at Tableau!)...they also have 2 cousins (INCLUDE and EXCLUDE LoDs), These are a halfway house between totally fixing the level of aggregation at which a calc is run, and the VizLoD. They are more efficient than FIXED LoDs (in terms of processing time/power), but are a little trickier (conceptually) to set up....certainly worth checking out.

11. Re: Help with calculating a group of data
Sam Bloodgood Nov 18, 2016 9:44 AM (in response to Simon Runc)I will will check them out. I also read your "Quora" reference and will read the other links today...

12. Re: Help with calculating a group of data
Simon Runc Nov 18, 2016 9:54 AM (in response to Sam Bloodgood)It might be that you want these calculated by week, and your data is at day level...so you need to let Tableau know.
So for example...to have this calculated at week level...
{FIXED[Hfitem], DATETRUNC('week',[Ivc Date]):SUM([SHIP_QTY])}
Also you might have other dimensions slicing the data that you either want (as with date), which you'll need to add to the LoD.
The first thing I ask about any data set I have, is what is the "Grain"?...or (another way) "What does a single row of my data represent?". So in the Superstore sales...one row represents an OrderID and Item (in that every OrderID/Item combination is unique). Yes there is Date, Customer, Category...etc. but these don't add to the grain (Date and Customer are simply attributes of OrderID and Category is simply an attribute of Item). Once you answer this, you should be able to decipher at what Grain (or Level of Detail) do I need this calculation run at...and then you should have your answer.
My Flow is always...
Data LoD > Calculation LoD > Viz LoD
For completeness...this is an oversimplification....when using Aggregate, Table Calcs & Include/Exclude LoDs, I need to know the vizLoD I'm aiming for, in order to determine the best Calculation LoD...but as a general principle it helps me think over my datachallenges.
hope that helps

13. Re: Help with calculating a group of data
Sam Bloodgood Nov 18, 2016 12:24 PM (in response to Simon Runc)For some reason I cannot get the {FIXED[Hfitem], DATETRUNC('week',[Ivc Date]):SUM([SHIP_QTY])} "DATETRUC" entered into the calculation. I get an error message

14. Re: Help with calculating a group of data
Simon Runc Nov 18, 2016 12:50 PM (in response to Sam Bloodgood)Ah it's only from Tableau 10, that we can nest calculations in LoDs...
If you first create a separate calculated field for DATETRUNC('week', [Ivc Date]), and then use that in the LoD instead that'll do the trick