Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
keithcurley
New Contributor II

Cumulative Chart Expression with Mont Dimension

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?

JanFebMarAprMayJunJulAugSepOctNovDec
Demand101055810203010000
Capacity888888888888
Backlog241-2-20123436282012

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

Regards,

Keith

1 Solution

Accepted Solutions

Re: Cumulative Chart Expression with Mont Dimension

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.

View solution in original post

14 Replies

Re: Cumulative Chart Expression with Mont Dimension

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

keithcurley
New Contributor II

Re: Cumulative Chart Expression with Mont Dimension

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

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.

View solution in original post

keithcurley
New Contributor II

Re: Cumulative Chart Expression with Mont Dimension

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

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

keithcurley
New Contributor II

Re: Cumulative Chart Expression with Mont Dimension

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

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?

keithcurley
New Contributor II

Re: Cumulative Chart Expression with Mont Dimension

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

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?