Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello - I've searched the forums about this question but haven't arrived at a clear answer yet:
Say I have a pivot table that has one dimension as a row, and TWO columns: first by month (Jan, Feb, Mar), then by year. What I’m trying to do is calculate YoY within month. When I add an expression to the pivot table, it is calculating for each year, when I want it to calculate by month. Plus not 100% sure of the proper syntax.
The data has more than two years, so the calculated column has to be “smart” enough to handle whichever two years the user chooses.
Any ideas? Example of what the final pivot table should look like - the first row is just for clarity indicating the month column.
Jan | Jan | Jan | Feb | Feb | Feb | |
Year1 | Year2 | YoY | Year1 | Year2 | Yoy | |
Dimension value1 | ||||||
Dimension value2 |
Thanks for any tips you can provide!
there are some based on ROW
using above function try column 1/column2
there is function Column(1)/Column(2)
may be
Tried that and got zeros. So am trying something different - the measure below I've added to return the amount for max year. I have it where month is the only column. The expression "Yr" has the year. The set expression below is not recognizing max year. Instead it is summing across all years. Any ideas?
Sum({$<Yr={$(=max(Yr))}> }amount)