Sum with Selections, Sum-if and Set Analysis Producing Different Results
Hi everyone,
I'm seeing something very odd in my application where a sum-if is returning an unexpected result as compared to using selections or set analysis.
I translated my application code into a sample using inline values. The code is below and the application is attached as well.
My application needs to use sum-if in the expressions within its pivot tables and unfortunately I can't use set analysis or require the user to click on a particular value.
The pivot table in my sample app returns the following values:
Submission
Sum (YearValue)
sum(if(Submission='FY09 April OL', YearValue))
sum({$<Submission={"FY09 April OL"}>} YearValue)
FY09 April OL
25555
102220
25555
As you can see above, the regular sum and set analysis return the same value. However, the sum-if for some reason does not act the same way. This pivot table has one dimension and its my submission field. The expressions basically replicate three scenarios. The first is a plain sum assuming the user will click on the submission entitled "FY09 April OL". In the sample app I only have one submission. The second expression is using a sum-if where the Submission is hard coded to "FY09 April OL" (in my actual app i have a disconnected table where a user selects a Submission). This returns a product of the Submissions times four. I'm assuming it's doing this because the table that contains the Submission has duplicates (one for each quarter). The last expression uses set analysis and returns the expected result.
Now I can't use the distinct qualifier in my sum-if to make this work because there could be situations where two values are identical but belong to different areas and I don't want to ignore those.
Here's the in-line code:
YearFact: LOAD * INLINE [ YearKey, YearValue FY09 April OL|Total Revenue, 23423 FY09 April OL|EBT, 2132 ;
QuarterFact: LOAD * INLINE [ QuarterKey, QuarterValue FY09 April OL|Total Revenue|Q1, 121 FY09 April OL|Total Revenue|Q2, 222 FY09 April OL|Total Revenue|Q3, 343 FY09 April OL|Total Revenue|Q4, 422 FY09 April OL|EBT|Q1, 242 FY09 April OL|EBT|Q2, 131 FY09 April OL|EBT|Q3, 1231 FY09 April OL|EBT|Q4, 12 ];
LinkTable: LOAD * INLINE [ YearKey, QuarterKey, Measure, SubmissionKey FY09 April OL|Total Revenue, FY09 April OL|Total Revenue|Q1, Total Revenue, FY09 April OL|Q1 FY09 April OL|Total Revenue, FY09 April OL|Total Revenue|Q2, Total Revenue, FY09 April OL|Q2 FY09 April OL|Total Revenue, FY09 April OL|Total Revenue|Q3, Total Revenue, FY09 April OL|Q3 FY09 April OL|Total Revenue, FY09 April OL|Total Revenue|Q4, Total Revenue, FY09 April OL|Q4 FY09 April OL|EBT, FY09 April OL|EBT|Q1, EBT, FY09 April OL|Q1 FY09 April OL|EBT, FY09 April OL|EBT|Q2, EBT, FY09 April OL|Q2 FY09 April OL|EBT, FY09 April OL|EBT|Q3, EBT, FY09 April OL|Q3 FY09 April OL|EBT, FY09 April OL|EBT|Q4, EBT, FY09 April OL|Q4 ];
Submissions: LOAD * INLINE [ SubmissionKey, Submission, PreviousSubmission, PreviousQuarter, FiscalYear FY09 April OL|Q1, FY09 April OL, FY09 Jan OL, FY08 Q4, FY09 FY09 April OL|Q2, FY09 April OL, FY09 Jan OL, FY09 Q1, FY09 FY09 April OL|Q3, FY09 April OL, FY09 Jan OL, FY09 Q2, FY09 FY09 April OL|Q4, FY09 April OL, FY09 Jan OL, FY09 Q3, FY09 ];
You could consider this a data model problem or an expression problem.
YearValue is for an entire year, but Submissions are by quarter.
With sum(YearValue) and the set analysis expression, you just get the total from the YearFact table based on that selection, which returns the correct total of 25555. With the IF, it's navigating to the YearFact table for every quarter, and all four quarters match, so you end up with four times the amount.
As an example data model "fix", you could allocate the YearValue to the quarters. As an example of an expression "fix", you could divide by the number of quarters, either by 4 if you know you always have all four quarters, or by a count of the number of quarters if it can vary. So for instance:
sum(if(Submission='FY09 April OL',YearValue )) / count(if(Submission='FY09 April OL',Submission))
Or just use the set analysis expression, which would execute more quickly anyway.
