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]

Hi @manoranjan_321988 

You never answered my question above, are you using a Bar chart of a Combo chart?

I have the Show measure if option in the Bar chart but not in the Combo chart

[manoranjan_321988]

Hi olofmalmstrom, good morning,

Back to my  work , will update you soon 

[Vegar]

I think the issue here could be your expression not doing what you expect.

Please verify that your set analysis work as you expect by testing it without the outer IF statement. You might want to change it to something like this.

Sum({<Fact_flag={'fact_1'},

Date = {">='$(=MonthStart(AddMonths(Today(), -12)))'<='$(=MonthEnd(AddMonths(Today(), -1)))'"}>}Measurefield)

[manoranjan_321988]

I am using bar chart only , my  qlik hib version is 14.129.23

could you pls  tell me your qlik version by clicking on Qlik hub about

[manoranjan_321988]

this calculated dimension giving the result  but in one scenario its getting failed

example from product table  which act as  dim table in Data model, i am selecting a  pdt "A" as filter in the front end from the dim table , for that pdt it have only  two months data in the fact table, now the chart shows only 2 months data of apr and march 2024, where others months in the axis label not showing, pls suggest on this 

[manoranjan_321988]

tried not showing any data

[manoranjan_321988]

Due to my low version I don't have and using bar chart only

[manoranjan_321988]

@olofmalmstrom 

waiting for your reply for this scenario 

[manoranjan_321988]

@olofmalmstrom any updates for this issue 

[olofmalmstrom]

Sorry for late reply.

It will only show the bars where you have data related.

If you add a line to you calendar like this:


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,
0 as dummy;
LOAD
"Date",
Date(MonthYear,'YYYY-MM') as MonthYear
FROM [lib://DataFiles/MasterCalenderdata-table.xlsx]
(ooxml, embedded labels, table is [MyWorkSheet-1]);

Then in your forumla for the bar, add

+ sum({1} dummy)

Your dimension will then be:

=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)
)

Your Measure:

Sum({<Fact_flag={'fact_1'}>}Measurefield) + sum({1}dummy)

Good luck 🙂 

Best Olof