QLIK PIVOT table - Measure Filtering -Rolling months

[Sathish_P]

Hi All,

I would like to apply filter in PIVOT table in order to strict last 3 months data for the attached table.

Please suggest me where to apply filter to restrict last 3 month data.

Thanks

[sunny_talwar]

You can use a calculated dimension or use set analysis.... for more help would you be able to share a sample?

[Sathish_P]

Hi Sunny Talwar

Thanks for helping me .. I appreciate you.

Please refer the attached QVF and help me. Thanks in advance.

Thanks

Sathish

[sunny_talwar]

Replace Month dimension with this

=If(Date#(Month, 'MMM-YY') >= AddMonths(Max(TOTAL Date#(Month, 'MMM-YY')), -2), Month)

Also, make sure to uncheck 'Include Null Values' for the above dimension to get this

[Sathish_P]

Hi Talwar

I appreciate you.. but it should render last 2 months based on the filter pane selection. For ex: If I select  Jun 2018 from Filter pane, Pivot table should get reflected May 2018, Jun 2018 Data only as Month dimension and corresponding data in the measure.

Thanks

Sathish

9994553190

Attached the filter version.

[sunny_talwar · Accepted solution]

Try this

Row Dimensions

ParentCustomer
Metrics_Desc

Column Dimension

=If(Date#(Month, 'MMM-YY') >= AddMonths(Max(TOTAL Date#(Month, 'MMM-YY')), -1), Month)

Expression

=if ( Only({<Month>}Metrics_Desc)='X', Num(Sum({<Month>}X),'##.00'),
	if (Only({<Month>}Metrics_Desc)='Y',Num(sum({<Month>}Y),'##.00'),
		if (Only({<Month>}Metrics_Desc)= 'A',Num(sum({<Month>}A),'##.00'),
			if (Only({<Month>}Metrics_Desc)= 'Z',Num(AVG({<Month>}A),'##.00') & '%' ,
			if (Only({<Month>}Metrics_Desc)= 'D',Num(sum({<Month>}D),'##.00'),
				if (Only({<Month>}Metrics_Desc)=  'B',Num(avg({<Month>}B),'##'),
					if (Only({<Month>}Metrics_Desc)=  'C',Num(avg({<Month>}C),'##'),
                    	

    '')))))))
    

[Sathish_P]

Thanks Talwar.. You helped me lot !! Appreciate your skill .. Keep going !! :))

[Sathish_P]

Thanks Talwar.. You helped me lot !! Appreciate your skill .. Keep going !!