Color Expression - Pivot Table dont shows correctly color

[DS395]

Hello everyone,

I have created a table that shows me everything correctly so far. There I have created a RunningValue, with which I can create a query in a pivot table based on the data in the table, which highlights the values in red if they exceed a value. Everything looks correct in the table and is displayed correctly. If I use the color expression in the pivot table, this does not work. See screenshot.

Can anyone tell me what I am doing wrong? This is the 1:1 query as it is in the table and it works there.

Here is my query for the Running Value:

RANGESUM(ABOVE(TOTAL SUM(Quantity), 0, AGGR(ROWNO(), (Customer,(TEXT,Ascending)),(Item,(TEXT,Ascending)),(DDate,(NUMERIC,Ascending)))))

And here for the color formatting:

IF(RANGESUM(ABOVE(TOTAL SUM(Quantity), 0, AGGR(ROWNO(), (Customer,(TEXT,Ascending)),(Item,(TEXT,Ascending)),(DDate,(NUMERIC,Ascending))))) > Stock,RED(),NULL())

Best regards

DS395

[rubenmarin · Accepted solution]

Hi, in pivot tables you can have horizontal and vertical dimensions, Above() could work with horizontal dimensions, adding the values from rows, for vertical dimensions you can use Before(), which is similar but adding by columns instead of rows.

Try using Before instead of Above to see if the running value are calculated as you want.

[DS395]

Hi,

I changed it as you described and it worked:

IF(RANGESUM(BEFORE(TOTAL SUM(Quantity), 0, AGGR(ROWNO(), (Customer,(TEXT,Ascending)),(Item,(NUMERIC,Ascending)),(DDate,(NUMERIC,Ascending))))) > Stock,RED(),NULL())

Thank you very much.