topic Re: Bar chart to show only 12 months rolling in App Development

Bar chart to show only 12 months rolling

manoranjan_321988 — Thu, 28 Nov 2024 15:50:07 GMT

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

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Thu, 28 Nov 2024 17:08:35 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Thu, 28 Nov 2024 17:54:22 GMT

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

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Thu, 28 Nov 2024 17:59:46 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Thu, 28 Nov 2024 18:02:39 GMT

Thanks and Trying pls give me some amount of time

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Thu, 28 Nov 2024 18:04:32 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Thu, 28 Nov 2024 18:38:00 GMT

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

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Thu, 28 Nov 2024 18:45:06 GMT

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?

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Thu, 28 Nov 2024 18:45:36 GMT

wht will be the alternative way solution for this

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Thu, 28 Nov 2024 19:01:36 GMT

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

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Thu, 28 Nov 2024 19:38:49 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Fri, 29 Nov 2024 04:41:57 GMT

Hi olofmalmstrom, good morning,

Back to my work , will update you soon

Re: Bar chart to show only 12 months rolling

Vegar — Fri, 29 Nov 2024 05:51:27 GMT

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)

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Fri, 29 Nov 2024 10:22:47 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Fri, 29 Nov 2024 10:49:30 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Fri, 29 Nov 2024 12:37:19 GMT

tried not showing any data

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Fri, 29 Nov 2024 14:06:35 GMT

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

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Fri, 29 Nov 2024 15:48:54 GMT

@olofmalmstrom

waiting for your reply for this scenario

Re: Bar chart to show only 12 months rolling

manoranjan_321988 — Sat, 30 Nov 2024 13:51:59 GMT

@olofmalmstrom any updates for this issue

Re: Bar chart to show only 12 months rolling

olofmalmstrom — Sat, 30 Nov 2024 21:07:10 GMT

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:

Your Measure:

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

Good luck 🙂

Best Olof