Bar chart to show only 12 months rolling

[manoranjan_321988]

Hi team,

attached  is my sample data which have  fact and master calendar  and below  is my script and chart expression

Fact:
LOAD
Measurefield,
date(floor(num(Date))) as Date,
Fact_flag
FROM folder;

Dim_MC:
LOAD
date(floor(num(Date))) as Date,
WEEK(Date) AS Week,
YEAR(Date) AS Year,
MONTH(Date) AS Month,
DAY(Date) AS Day,
WEEKDAY(Date) AS Weekday,
'Q'& CEIL (MONTH( Date ) / 3 ) AS Quarter,
DATE(monthstart( Date ), 'MMM-YYYY') AS MonthYear,
WEEK(Date ) & '-'& YEAR (Date ) AS WeekYear

from folder;

bar chart dim and exp

dim as month year

exp as below

if(GetSelectedCount(Year)>=1 or GetSelectedCount(Quarter)>=1 or GetSelectedCount(MonthYear)>=1 or GetSelectedCount(Week)>=1 or GetSelectedCount(Date)>=1,

Sum({<Fact_flag={'fact_1'}>}Measurefield),

Sum({<Fact_flag={'fact_1'},
MonthYear = {">=$(=date(MonthStart(AddMonths(Today(), -12)),'MMM-YYYY'))<=$(=date(MonthEnd(AddMonths(Today(), -1)),'MMM-YYYY'))"}>}Measurefield)

)

i want to show the default 12 months rolling in my chart and i don't want to disable the data enabling ->  include zero values, because if there is no data for that month it have show 0 for the month since i am using master calender

now wht is happening in bar chart it shows entire all months here, it have to show only 12 months data as per the if loop section 

 note i dont want to disable the data handling  -->include zero values please help and support on this and also no calculated dim also 

[olofmalmstrom]

You can split it into two separate measure, and add the if statement to the show condition.

[manoranjan_321988]

Hi olofmalmstrom,

I can't use vizlib due to restriction while publishing, and also in the native qlik bar chart there is no show hide option pls let me know any other option 

Some times  rolling 12 months have partial month data If some other dim  product table filter are selected ,  at that time also it have to show the bars with values and no value bar has to show  x axis label (month year)with 0 value   y axis , this is  also not achieved in the vizlib bar chart 

Pls suggest some other way

[olofmalmstrom]

You can do it in Qlik Native using the "Show measure if" condition

[manoranjan_321988]

Thanks and Trying pls give me some amount of time

[olofmalmstrom]

I've attached a sample where you can see it working

[manoranjan_321988]

my bad time, i don't have the option to show / hide measure  in my bar chart , even i searched for edit sheet enable advanced option also not there in my QSEW

[olofmalmstrom]

Are you using the Bar chart or the Combo chart?

In my QSEW i have the option for bar chart but not for combo chart.

Are you on an old version of Qlik Sense?

[manoranjan_321988]

wht will be the alternative way solution for this

[olofmalmstrom]

The alternative is to limit the dimension values using a calculated dimension like this:

=If( GetSelectedCount(MonthYear)>=1 or GetSelectedCount(Date)>=1,
MonthYear,
IF(MonthYear>=date(MonthStart(AddMonths(Today(), -12)),'YYYY-MM') and MonthYear <=date(MonthEnd(AddMonths(Today(), -1)),'YYYY-MM'), MonthYear)
)

Also uncheck the Include null values on the dimension