Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
mrainford
New Contributor III

Best approach for QTR and YTD in Pivot table

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?

ProductMonthOCTNOVDECJANFEB    Quarter 1Quarter 2Quarter 3Quarter 4Y-T-DPrior Year
CarHonda553375434565    41000446410 
 Ford5.0234.02345.00.0345.0    3450.00.02584345 
 Mazda234.0432.042.05.030.0    350.00.070835 
 GM-C33.023.043.0543.056.0    5990.00.099599 
 rate-110.0%9.0%15.0%32.0%54.0%    0.860.00.00.3486.0% 
 rate-225.0%88.0%65.0%43.0%24.0%    0.670.00.01.7867.0% 

 

3 Replies
Shubham_Deshmukh
Valued Contributor

Re: Best approach for QTR and YTD in Pivot table

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

 

mrainford
New Contributor III

Re: Best approach for QTR and YTD in Pivot table

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?

 

Shubham_Deshmukh
Valued Contributor

Re: Best approach for QTR and YTD in Pivot table

I am not getting what you are saying.