Hello Cognoise community,
I need to create a query calulcation that adds the values from previous column row, to show available capacity for current week and forward . For example see illustration below:
W1 W2 W3
Quantity 5 12 10
Capacity 15 20 15
Available cap 10 8 (+10) =18 5 (+18) = 23
The data source is relational.
All help and ideas are greatfully accepted
Best regards,
Oscar
Should your Available cap for W3 be 5 (+ 18) = 23 ?
From Quantity 10 and Capacity 15 ---> (15 - 10)
Yes Jan, I typed wrong but corrected it. Thanks for the notice.
Try something like:
W1 W2 W3
Quantity 5 12 10
Capacity 15 20 15
Available cap 10 8 (+10) =18 5 (+18) = 23
[Available cap]=[Capacity]-[Quantity]
[Cumulative Available cap] = running-total([Available cap] for [Week])
References:
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_fm.10.2.2.doc/c_ces_runningtotal.html#ces_runningTotal (https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_fm.10.2.2.doc/c_ces_runningtotal.html#ces_runningTotal)
https://www.ibm.com/support/pages/how-create-running-total-cumlative-balance-balance-forward-type-report (https://www.ibm.com/support/pages/how-create-running-total-cumlative-balance-balance-forward-type-report)
oscarca - You are welcome.
Please let us know (utilizing your data), if Andrei I's recommendation works.
Thank you Andrei I. Always good to learn.
Thank you, Bob (aka Jan)
I tried the suggested solution from Andrei but didnt really seem to work. See attached image.
The desired result for Cumulative available cap for week 48 should be -25,226 (i.e the same as available cab) and for week 49 it should be 25,075 + (-25,226) = -0.151
Do you have any other dimensions/attributes/groupings in the query for this crosstab besides Week?
Only [Work center] to the left of the measure rows
W48 W49
Work center Quantity
Try :
[Cumulative Available cap] = running-total([Available cap] for [Work center])
You also might need to change Data Items Aggregation ( e.g. Default or Summarize)
See an attached example built on the Cognos 11.1 R4 (IBM Cloud trial)
When changing the aggregation property to Summarize on both [Quantity] and [Capacity] it solved the issue. Thank you Andrei, very much appreciated.
Thank you Andrei I and oscarca.
Happy Thanksgiving from USA.
The same to you Jan !