
1. Re: Calculations between 2 worksheets using the same data but different filters
Daniel Vincent Nov 5, 2015 12:56 PM (in response to Bryan Mills)You'll need to use LoD calcs with context filters. Can you post up a sample dataset/packaged workbook and if need anonymize the data or recreate in Superstore.

2. Re: Calculations between 2 worksheets using the same data but different filters
Bryan Mills Nov 5, 2015 1:35 PM (in response to Daniel Vincent)So here it is. Should be pretty selfexplanatory once you see how I have it set up and should hopefully see what I'm looking to do (calculate the difference between A and B).
Let me know if there are problems with that file. Thanks!

Compare Calculation Example.twbx 97.2 KB


3. Re: Calculations between 2 worksheets using the same data but different filters
Bryan Mills Nov 9, 2015 9:51 AM (in response to Daniel Vincent)Did you have a chance to look at the workbook I posted? If not no worries, just checking.
If anyone else wants to take a look I'd love your help. Thanks!

4. Re: Calculations between 2 worksheets using the same data but different filters
swaroop.gantela Nov 9, 2015 4:35 PM (in response to Bryan Mills)Bryan,
Filters and LoD is most likely the best way to go, but I was wondering if using parameters could work for your setup.
It has its drawbacks, but potentially could have some benefit too.
There would be two identical parameters of each type ( [EthnicityA], [EthnicityB]...) being string lists of the respective field, and then you would perform a separate calculation of A and B like:
[Met Std Calc A]:
IF [Grade]=[GradeA]
AND [Test]=[TestA]
AND ...
THEN [Med Std Calc]
END
The drawback is that the parameter wouldn't dynamically change with new entries to each field.
Though it could most likely be done with filters too, I think the parameter method allows you
to put both line graphs (and the difference) on the same chart, if that would be useful.

193241paramB.twbx 423.4 KB


5. Re: Calculations between 2 worksheets using the same data but different filters
Bryan Mills Nov 10, 2015 9:02 AM (in response to swaroop.gantela)This looks great! I'm going to have to dig in deeper and see if it might work for me with some adjustments and added parameters
I got help a little while ago using parameters as the solution on a similar request but the problem with the parameters (for me, at least) was in the handling of ALL values. For example, I have a field that checks for English as a 2nd language (a Y or N) and when the user selects YES or NO it properly displays just those students but when I choose the option for ALL (in other words, show both the Y and N values in the same series) it fails. I know adding the ALL functionality to parameters has been the top user suggested Tableau feature for quite a while and your solution (and the one someone else assisted me with) would work perfectly if they'd handle the ALL option properly, but thus far I'm just not exactly getting it to work. Do you have any experience with the solution you sent and including an option to display ALL values for that parameter?
Thanks again for the help!

6. Re: Calculations between 2 worksheets using the same data but different filters
swaroop.gantela Nov 10, 2015 9:40 AM (in response to Bryan Mills)Bryan,
My apologies for not paying attention to the Alls.
I think it can still work by just adding "All" to the parameters and then
adding All to the calculation with enclosing parentheses:
IF ([ContainerAall]="All" OR [Container]=[ContainerAall])
AND ([CustomerSegmentAall]="All" OR [Customer Segment]=[CustomerSegmentAall])
...
THEN [Sales] END
Maybe Nulls can be handled similarly.

193241paramC.twbx 462.8 KB


7. Re: Calculations between 2 worksheets using the same data but different filters
Bryan Mills Nov 10, 2015 10:25 AM (in response to swaroop.gantela)Wow, this looks like it's going to work...no clue why I was having such a hard time before with ALL values, it just never really translated easily I guess. I'm implementing the logic you have in here into my own workbook and will report back with my progress, perhaps post a copy of my workbook if I'm getting tripped up. But, the way you handle the ALL values here is very clean and deceptively simple; if I can replicate it with several parameters and get it to accurately calculate the numerator and denominator in my "met standard" calculation I should be all set. Well done and thanks again!

8. Re: Calculations between 2 worksheets using the same data but different filters
Bryan Mills Nov 10, 2015 11:29 AM (in response to swaroop.gantela)Check out the attached workbook. I used the logic from your example on my data and I'm running into the same problem I've run into before: the all option displays a series for each value. So in my example, when you select ALL for the ELL Param A you now see a series for Y and another for N, it's not showing it as a single series containing all values. I'm guessing the problem is in how I calculate the met std:
IF ([ELL Param A]="All" OR attr([Bilingual ESL])=[ELL Param A])
AND ([Gender Param A]="All" OR attr([Gender])=[Gender Param A])
THEN COUNT (IF([Met Std] = 1) THEN 1 END) / COUNT([Met Std]) END
In the data, met std is a 1 if they met std (what I'm trying to figure out) or a 0 if they did not and so I do a count for the numerator. In your example I see that you just use a SUM function on sales in your example...could I perhaps alter this so I do a sum on met std. and then use the # of records to determine the denominator? Would that change anything? My application is always tough to relate to superstore since I need to aggregate/count and such rather than just summing up the sales and so I struggle with the great help here because solutions always require me to view it all a bit differently (oh how I wish I was just summing up sales!). Thanks for your patience.

Compare Calculation Example.twbx 109.5 KB


9. Re: Calculations between 2 worksheets using the same data but different filters
swaroop.gantela Nov 10, 2015 12:05 PM (in response to Bryan Mills)1 of 1 people found this helpfulBryan,
I'll need to play around with it more, but I do agree with you that there might be
promise in removing the aggregation from the calculation.
I don't think you need to even sum it in the calc:
IF ([ELL Param A]="All" OR [Bilingual ESL]=[ELL Param A])
AND ([Gender Param A]="All" OR [Gender]=[Gender Param A])
THEN [Met Std]
END
And then afterwards just sum the Calc and divide it by the count?
Will look into it some more.

10. Re: Calculations between 2 worksheets using the same data but different filters
Bryan Mills Nov 10, 2015 1:24 PM (in response to swaroop.gantela)1 of 1 people found this helpfulSuccess! See the attached workbook. I looked at the one you sent with the ALL values for the differences between the 2 and noticed you had wrapped the items on the marks shelf in attr() so I did that and then ditched the calculation in the formula so it now looks like this:
IF ([ELL Param A]="All" OR [Bilingual ESL]=[ELL Param A])
AND ([Gender Param A]="All" OR [Gender]=[Gender Param A])
THEN [Met Std] END
and I changed the measure value like this:
[ALL Calc A] / [Number of Records]
and all of a sudden SUCCESS! I'm not exactly sure why wrapping the marks on the shelf in attr() fixed everything but there it is. This is EXACTLY what I was looking to do and I can now start adding more parameters to deal with the rest of the dimensions I'd like to filter on as well as some userdefined filters that I'd like to be applied to both the "compare" series. Good stuff.
Thanks a ton for the help!

11. Re: Calculations between 2 worksheets using the same data but different filters
swaroop.gantela Nov 10, 2015 1:34 PM (in response to Bryan Mills)Bryan,
Glad you were able to sort it through.
Nice work!

12. Re: Calculations between 2 worksheets using the same data but different filters
Holly Ftizgerald May 24, 2017 10:05 AM (in response to swaroop.gantela)This solution is exactly what I needed to do! I cannot thank both of you enough.
I have been working for while to allow a user defined (filtered) subset of a population to be compared and used in calculations with the total population.