Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Instead of using 'Full Accumulation' use RangeSum(Above/Before())) function to do this.... what exact expression are you using right now?
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?
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.
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
That is exactly what the above should do for you... have you given it a try yet?
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
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?
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
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?