Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Pick and match not working with 12 rolling months

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

 

8 Replies
Highlighted

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.

Highlighted
Contributor
Contributor

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.

 

Highlighted

Check attached

Highlighted
Contributor
Contributor

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

Highlighted

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

Highlighted
Contributor
Contributor

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

Highlighted

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')//
)
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.