topic Re: Cumulative Chart Expression with Mont Dimension in QlikView

Cumulative Chart Expression with Mont Dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

I have two data points, Capacity and Demand, which I display monthly on a straight table chart.

Using this data, I have created a simple expression which subtracts Capacity from Demand to calculate under/over utilised in the month. This expression also has 'Full Accumulation' so I can track if there is a building 'Backlog' month on month.

My issue is, I want to calculate and display the rolling the Backlog number but I only want to start displaying the data from a certain month.

To explain further via example, I have Capacity and Demand data from Jan-Dec generating the rolling Backlog through the year but I only want the table to display months from April forward. If I restrict the Month dimension via Calculated Dimension then my expression will not take into account (or see) months Jan-Mar.

The table below illustrates what the full year would look like, but can I display the same numbers but only from Apr onward?

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Demand	10	10	5	5	8	10	20	30	10	0	0	0
Capacity	8	8	8	8	8	8	8	8	8	8	8	8
Backlog	2	4	1	-2	-2	0	12	34	36	28	20	12

Does anyone know how I can achieve this requirement? Thank you in advance for any support.

Regards,

Keith

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Fri, 02 Feb 2018 15:24:07 GMT

Instead of using 'Full Accumulation' use RangeSum(Above/Before())) function to do this.... what exact expression are you using right now?

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Fri, 02 Feb 2018 15:43:00 GMT

Hi Sunny,

But if I implement a RangeSum expression and apply a filter on the Month dimension using a calculated Dimension (if Month >= April, Month), then the RangeSum will not capture months Jan-Mar as these will not now be present in the table.

Does that make sense?

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Fri, 02 Feb 2018 15:48:12 GMT

There is a work around available using set analysis. Difficult to give an exact expression without knowing much about your data... but something like this

RangeSum(Above(Sum({<Month>} Measure), 0, RowNo())) * Avg({<SetAnalysis4SeeingMonth'sAfterApril>} 1)

So, the first part of the expression (RangeSum) will accumulate from the start. But the second part will only be 1 for the months you have specified in your set analysis and 0 otherwise.

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Tue, 06 Feb 2018 16:10:52 GMT

Hi Sunny,

But I still need to include the values of the months before April, I just don't want those values displayed in the table.

For example, in the example table I posted with my initial question, I want to display that table exactly as-is but to only show months from April onwards. The key point is that the Backlog number, which is a rolling cumulative total of Capacity & Demand, should still be taking Jan, Feb, Mar data into account when calculating the April total to display.

Does that make sense? Apologies if it wasn't clear previously.

Thanks,

Keith

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Tue, 06 Feb 2018 16:14:23 GMT

That is exactly what the above should do for you... have you given it a try yet?

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Thu, 08 Feb 2018 17:01:50 GMT

Hi Sunny,

Apologies for doubting you!

I am playing around with the expression now and have nearly got it to work. I still don't fully understand the workaround though...

Will update the response with correct answer once I complete it.

Thanks,

Keith

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Thu, 08 Feb 2018 17:06:31 GMT

Apologies for doubting you!

I am sure you were not doubting me , but even if you were, that is okay.

Anyways, we might be able to help if you can explain what you got to work and what is not working?

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Fri, 09 Feb 2018 15:51:45 GMT

Hi Sunny,

I have the expression correctly written now and it's outputting the figures as expected.

However I have one final issue. The months before April are appearing as null in the table with character '-' but I cannot get these columns to be suppressed. For some reason the suppress null values (and I have also tried to convert these to zeros) does not seem to suppress them.

Is this something that might be linked to using the AVG function approach?

Thanks again,

Keith

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Sun, 11 Feb 2018 00:54:07 GMT

Avg function should not be doing this, but QlikView version might have to do something with it. Would you be able to share an image of what you are seeing?

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Mon, 12 Feb 2018 08:59:46 GMT

Hi Sunny,

Yes it does seem strange. I have attached screenshots below of what I am seeing and related settings.

Thanks,

Keith

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Mon, 12 Feb 2018 13:24:43 GMT

This seems like a QlikView version issue to me. I don't really know.

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Tue, 13 Feb 2018 12:45:15 GMT

Hi Sunny,

I published the application to AccessPoint this morning and thankfully the issue above did not reoccur there.

Thank you for your help with this issue, very much appreciate the support (and patience!).

Regards,

Keith

Re: Cumulative Chart Expression with Mont Dimension

Anonymous — Tue, 13 Feb 2018 12:47:15 GMT

How do I mark your response as the correct answer Sunny?

Re: Cumulative Chart Expression with Mont Dimension

sunny_talwar — Tue, 13 Feb 2018 13:15:35 GMT

No problem at all... I am glad it finally got resolved.

Qlik Community Tip: Marking Replies as Correct or Helpful‌

Best,

Sunny