Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
yogitamehta10
Creator
Creator

to show last 12 month in bar chart

HI All,

i have a requirment , i need to show last 12 months data by defualt.

my expression is :

Count({1<Compliant={'Y'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'N'},

[MONTH YEAR Calender]=,Status=>}[ UID])

my dimension is [MONTH YEAR Calender]


Format of dimension is mmm-YYY.



Plaese help ?

6 Replies
sunny_talwar

May be this:

Count({1<Compliant={'Y'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'N'},

[MONTH YEAR Calender] = {"$(='>=' & Date(AddMonths(Max([MONTH YEAR Calender]), -12), 'MMM-YY') & '<=' & Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YY'))"}, Status=>} [ UID])

yogitamehta10
Creator
Creator
Author

not working ..

Please review it ?

Count({1<Compliant={'Yes'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'NEW'},

[MONTH YEAR Calender] = {"$(='>=' & Date(AddMonths(Max([MONTH YEAR Calender]),-12), 'MMM-YYYY') & '<=' &

Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YYYY'))"},Status=>}[MPS TXN UID])

sunny_talwar

Can you add this

='>=' & Date(AddMonths(Max([MONTH YEAR Calender]),-12), 'MMM-YYYY') & '<=' & Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YYYY')

In a text box object to see what date range is this showing and what is the format? Is this giving you the right range and right format?

Second thing to check is if you have other date and time related field where you are making selections? If there are other fields (for instance Month or Year), you will have to add them to your expression so that your expression doesn't filter out based on selection in those date and time related fields

Count({1<Compliant={'Yes'},[Fine Level]={'0'},Status= {'Accepted'},[Connection Type]={'NEW'},

[MONTH YEAR Calender] = {"$(='>=' & Date(AddMonths(Max([MONTH YEAR Calender]),-12), 'MMM-YYYY') & '<=' & Date(AddMonths(Max([MONTH YEAR Calender]), 0), 'MMM-YYYY'))"}, Status=, Month, Year>}[MPS TXN UID])

If this still doesn't resolve your issue, then I suggest that you provide more detail as to what isn't working and may be provide a sample to show what the issue is.

HTH

Best,

Sunny

nickm
Contributor
Contributor

Here is one of many ways to solve this requirement: Add this to your calendar script to create a Rolling 12 month flag - this will create through last closed period - ie: September 2015 - August 2016:

-1 * (%_DateKey>= MonthStart(AddMonths($(vToday), -12)) AND %_DateKey<MonthStart($(vToday)))        as _Flag_R12,

Then add this to your expression :

sum({<_Flag_R12 = {'1'}>} Sales)

If you provide a sample file I can insert and forward to you.

Thx - Nick

nickm
Contributor
Contributor

Sorry, just add the _Flag_R12 = {'1'} to your current expression.

Not applicable

Hi,

You can use a dimension like this one,


=Date( monthstart(firstDate), 'MMM-YYY')

and use the "firstDate" data for get the first day (12 months ago). You can calculate it from the function Today().

I hope you find it useful!