topic Re: Month Vs Rollign 12 months in combo chart in App Development

Month Vs Rollign 12 months in combo chart

manoranjan_d — Sat, 16 Nov 2024 03:17:00 GMT

Hi all,

I have requirement to have a combo chart with month-wise date vs 12 months rolling. attached the screenshot.

the screenshot dimension has to be the last 12 months and the bar has to be month-wise data and line or dot has to be 12 months rolling.

I have achieved the dimension and month-wise data but the rolling 12 months not happening

example

dim have last 12 months Jan18-dec 18-> this is achived by selecting the limitation--> fixed number--> top as 12 .

exp 1 has been achieved based on the period I select.

if(GetFieldSelections(Period)='12 Months Rolling',
sum({<Month_Year1={"$(vPeriodType)"},Month=,Year=>}[ Quantity]),

if(GetFieldSelections(Period)='YTD',
sum({<Month_Year1={"$(vPeriodType)"},Month=,Year=>}[ Quantity]),,

if(GetFieldSelections(Period)='Monthly',
sum({<Month_Year1={"$(vPeriodType)"},Month=,Year=>}[ Quantity])

)))

where my variable value is

=if(GetFieldSelections(Period)='12 Months Rolling',
'<=$(=Date(MAX(Month_Year1),'MMM-YY')) >=$(=Date(AddMonths(MAX(Month_Year1),-11),'MMM-YY'))',
if(GetFieldSelections(Period)='YTD',
'<=$(=Date(MAX(Month_Year1),'MMM-YY')) >=$(=Date(YearStart(MAX(Month_Year1)),'MMM-YY'))',
if(GetFieldSelections(Period)='Monthly',
'<=$(=Date(MAX(Month_Year1),'MMM-YY')) >=$(=Date(MonthStart(MAX(Month_Year1)),'MMM-YY'))')))

exp 2 as a line chart is not achievable where Jan 18 dimension value shd have the data from feb17-jan18 as 12 months rolling and similarly to the Feb 18 as mar17-feb18.

exp 2 is not achievable. pls suggest and help me how to achieve the 12 months rolling in this scenario.

Regards

Mano

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 11:04:57 GMT

Hi all,

waiting for your valuable response.

Re: Month Vs Rollign 12 months in combo chart

sunny_talwar — Thu, 13 Feb 2020 12:40:46 GMT

Do you have The As-Of Table created in your script? If you do you can use that or you can use RangeSum with Above

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 14:01:30 GMT

Hi Sunny,

Many thanks for the Response.

I didn't use "as of now" since its single simple excel table and attached the sample.

I tried with range sum above function for the second expression(Dot or line chart), but it's not working since it considering the dimension and I tried avoiding the dimension again it's not showing the correct value.

exp2:

=RangeSum(Above( total Sum( [Quantity]),0,12))

what happening is jan2019 should has the value from Feb 2018 to jan2019 but it's taking only Jan2019 as starting value

script is below

T1:

load *,
date(date#(Month_Year,'MMM-YY'),'MMM-YY') as Month_Year1;
LOAD
Year(date#("Reporting Year",'YY')) as Year,
date(date#("Reporting Period",'MMM'),'MMM') as Month,
date(date#("Reporting Period",'MMM'),'MMM')&'-'&Year(date#("Reporting Year",'YY')) as Month_Year,

Quantity from xxx path.

Period_Type:
Load * Inline
[
Period
12 Months Rolling
YTD
Monthly
];

chart is combo

dim is

date(Month_Year1,'MMM-YY')->last 12 months

exp 2 as a dot or line above mentioned and

exp1 as a bar and its month-wise data and variable already explained.

please help me with this exp 2 to achive it.

Re: Month Vs Rollign 12 months in combo chart

sunny_talwar — Thu, 13 Feb 2020 14:14:35 GMT

Seems to be working here

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 14:36:16 GMT

sorry sunny, let me give with revised sample data file which has 2017,2018 & 19 data.

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 15:13:25 GMT

Hi Sunny,

Attached my qvf file where the Jan 2019(exp2) starting point has 72.9 it has only Jan19 month value(this is the roadblock), but it should have sum of value-form feb18 to Jan 19, this will have a clear picture to you. kindly help me with this

Re: Month Vs Rollign 12 months in combo chart

sunny_talwar — Thu, 13 Feb 2020 15:13:46 GMT

What was the issue with the previous one?

Re: Month Vs Rollign 12 months in combo chart

sunny_talwar — Thu, 13 Feb 2020 15:19:39 GMT

You were putting limits on your dimension... see attached

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 15:58:53 GMT

many Thanks, sunny for your solution it's a great help

I implemented the same, the values are getting accurate.

But facing one more issue, I m getting the dimension value from Jan 2017 to Dec 2019, I avoided the dim limit as per your qvf and given no limitation and data handling disabled the include zero values but it's coming with Jan 2017 to Dec 2019. kindly help me with this so that my requirement will be accurate.

Re: Month Vs Rollign 12 months in combo chart

sunny_talwar — Thu, 13 Feb 2020 17:14:21 GMT

Uncheck 'Include zero values' under Add-ons -> Data handling

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 18:42:02 GMT

Hi sunny,

In the exp 2 I changed the condition to =If(Column(1) > 0,previous it was =If(Column(1) <> 0, many thanks for the help and great solution. now my requirement satisfied 100%.

Regards

Mano

Re: Month Vs Rollign 12 months in combo chart

manoranjan_d — Thu, 13 Feb 2020 18:44:50 GMT

i got my solution based on your input, may be your solution can help this thread

https://community.qlik.com/t5/Qlik-Sense-App-Development/Show-Rolling-12-Months-of-data-in-Combo-Chart/m-p/9480

Re: Month Vs Rollign 12 months in combo chart

Vasunthra_R — Mon, 17 Apr 2023 15:14:49 GMT

Hi Sunny,

I have a same case to restrict the dimension to show only 12 months from Max date selected but the Rolling 12 months should be like for Mar 23 data should show from Apr22 to Mar 23, for feb 23 its from Mar 22 to Feb 23 like that it goes on.

I used Rangesum below expression to achieve the rolling data but not able to restrict the chart to show only 12 months of data. could you please help here.

RangeSum(Above(count({<[Enr Flag]={'1'},TableName={'Enrollment'}>}distinct Pat_CP_ID),0,12))