Hi!,
I'm trying to build a Current vs. Prior variance table in Tableau, but I'm struggling with the setup of the Current and Prior columns. The packaged workbook is attached.
Users can select one or more Current Terms (example: 202611, 202615), and I have a mapping file that determines the corresponding Prior Terms
(Term - 100).
My mapping table contains:
- Current Term
- Prior Term
I'm unsure of the best approach to structure the calculations so that Tableau returns:
- Current Value
- Prior Value
- Variance
- % Variance
I first tried using calculations like:
{ FIXED [Term]-100, [Metric], [Group], [Type] :
SUM([Value])
}
but since Term is a string and users can select multiple terms, this approach became difficult to maintain.
I also experimented with creating Current and Prior calculated fields and using FIXED LOD expressions, but either:
- Prior returned the same value as Current,
- Prior returned NULL,
- or the calculations stopped working when multiple current terms were selected.
- #Tableau Desktop & Web Authoring
Use the mapping table to link Current Term → Prior Term instead of calculating [Term] - 100. Aggregate Current and Prior values separately, then calculate Variance = Current - Prior and % Variance = Variance / Prior. This approach also works better when users select multiple Current Terms.
