Calculation based on previous column rows ?

[oscarca]

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

[Cognos_Jan2017]

Should your Available cap for W3 be 5 (+ 18) = 23 ?


From Quantity 10 and Capacity 15 ---> (15 - 10)

[oscarca]

Yes Jan, I typed wrong but corrected it. Thanks for the notice.

[Andrei I]

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/pages/how-create-running-total-cumlative-balance-balance-forward-type-report

[Cognos_Jan2017]

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)

[oscarca]

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

[Andrei I]

Do you have any other dimensions/attributes/groupings in the query for this crosstab besides Week?

[oscarca]

Only [Work center] to the left of the measure rows

                                             W48           W49     
Work center     Quantity

[Andrei I]

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)

[oscarca]

When changing the aggregation property to Summarize on both [Quantity] and [Capacity] it solved the issue. Thank you Andrei, very much  appreciated.

[Cognos_Jan2017]

Thank you Andrei I and oscarca.

Happy Thanksgiving from USA.

[oscarca]

The same to you Jan !