Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
sunny_talwar

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

Anonymous
Not applicable
Author

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?

sunny_talwar

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.

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

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?

Anonymous
Not applicable
Author

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

sunny_talwar

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?