Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following pivot table in Q-View. Sample data provided below.
These are my Dimensions = Product and Month (based on Master Calendar)
I created 6 expressions to generate totals for a given month. I need to create Quarters, YEAR TO DATE and Prior Year for the corresponding expressions in the same pivot table. What is the best approach?
Product | Month | OCT | NOV | DEC | JAN | FEB | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | Y-T-D | Prior Year | ||||
Car | Honda | 55 | 337 | 54 | 345 | 65 | 410 | 0 | 0 | 446 | 410 | |||||
Ford | 5.0 | 234.0 | 2345.0 | 0.0 | 345.0 | 345 | 0.0 | 0.0 | 2584 | 345 | ||||||
Mazda | 234.0 | 432.0 | 42.0 | 5.0 | 30.0 | 35 | 0.0 | 0.0 | 708 | 35 | ||||||
GM-C | 33.0 | 23.0 | 43.0 | 543.0 | 56.0 | 599 | 0.0 | 0.0 | 99 | 599 | ||||||
rate-1 | 10.0% | 9.0% | 15.0% | 32.0% | 54.0% | 0.86 | 0.0 | 0.0 | 0.34 | 86.0% | ||||||
rate-2 | 25.0% | 88.0% | 65.0% | 43.0% | 24.0% | 0.67 | 0.0 | 0.0 | 1.78 | 67.0% |
Best approach would be to create variables for these parameters and use them like,
vQuarterOneStart = YearStart(Today()) vQuarterOneEnd = MonthEnd(Addmonths(YearStart(Today()),2)) vQuarterSecondStart = Addmonths(YearStart(Today()),3) vQuarterSecondEnd = MonthEnd(Addmonths(YearStart(Today()),5)) vQuarterThirdStart = Addmonths(YearStart(Today()),6) vQuarterThirdEnd = MonthEnd(Addmonths(YearStart(Today()),8)) vQuarterFourStart = Addmonths(YearStart(Today()),9) vQuarterFourEnd = MonthEnd(Addmonths(YearStart(Today()),11))
vYTD = YearStart(today()) // Same like Quarter1Start
vToday = (date(today())
Now use below exps according to your dates and quantity,
Quarter 1 =sum({<yourDateField={">=$(=vQuarterOneStart)<=$(=vQuarterOneEnd)"}>}sales) Quarter 2 =sum({<yourDateField={">=$(=vQuarterSecondStart)<=$(=vQuarterSecondEnd)"}>}sales) . .
Regards,
Shubham D
Thanks for responding. After looking into your suggestion the Quarter 1, Quarter 2... expressions will be a record under the established ones (e.g. Mazda,Rate-1, Rate-2) rather than columns alongside Month. How do I get an expression to be columns?