Pick and match not working with 12 rolling months

[pritishnayak4]

Hi All,

I need one help in achieving one result in which i need 13 months rolling with 2 condition.

This is the result which i am getting with this expression.

=pick(match([Prof6 Raw],'ABC'),

Num(sum({<[BI As Of Month]={">=$(=Date(addmonths(Max([BI As Of Month]), -12),'MMM-YY'))<=$(=Date(addmonths(Max([BI As Of Month]), 0),'MMM-YY'))"},Segment={'Auto'},Flag={'Prpop'}>}Value),'###,#00')//
)

Above expression is giving me extra null month columns which i want exclude , not by suppress zero because of chart background formatting. 

Thanks

[sunny_talwar]

I guess you can play around with your dimension and use a calculated dimension to exclude the [BI As Of Month] from your table... difficult to give your exact directions without looking at a sample file and trying it out.

[pritishnayak4]

Hi Sunny,

Thanks for suggestion,

But there are some points which i wanted to highlight ,

As per my project standards we cannot use calculated dimensions.

For your reference i am attaching the qlikview file with sample data.

[sunny_talwar]

Check attached

[pritishnayak4]

we cannot use calculated dimension in charts ,as per project standard policy.

[sunny_talwar]

So, you don't want to use calculated dimension and you don't want to suppress zero values? Would you always want to see the the most recent last 13 months and not want to change this based on selection? If that is true then there might be a way... else I am not sure how you would do this

[pritishnayak4]

Yes , user want to see the the most recent last 13 months and dont  want to change this based on selection. 

[sunny_talwar]

Here is another option where you create a new Month field in the script

check:
LOAD Date([BI As Of Month],'MMM-YY') as [BI As Of Month],
     Flag, 
     Name, 
     Segment, 
     Value
FROM [Book1.xlsx]
(ooxml, embedded labels, table is Document_TB02);

Left Join(check)
LOAD AddMonths(Max([BI As Of Month]), -12) as [13MonthsAgo],
	 AddMonths(Max([BI As Of Month]), 0) as [MaxMonth]
Resident check;

final_check:
LOAD *,
	 If([BI As Of Month] >= [13MonthsAgo] and [BI As Of Month] <= [MaxMonth], [BI As Of Month]) as [New BI As Of Month]
Resident check;

DROP Table check;

[Portfolio6 Raw]:
LOAD * INLINE [
    Name, Prof6 Raw
    	WA_FICO,			WA FICO	
];

and then use the new month field as your dimension with the following expression

=Pick(Match([Prof6 Raw],'WA FICO'),
Num(Sum({<Segment = {'Auto'}, [BI As Of Month], [New BI As Of Month]>}Value),'###,#00')//
)

[Brett_Bleess]

I may be way off here, but I wanted to toss this Design Blog post out just in case it may be of some use on this one:

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

If Sunny's last post and example worked, do not forget to use the Accept as Solution button on that post to give him credit and let others know that it worked.  

Regards,
Brett