Add Row Delta on a table that's grouped by a dimension?

[ForgotMyOldUsername]

Thanks in advance for any suggestions!

 Ideally I'd like to roll it up in the load script, rather than do it 'live', however , in chart would be a useful one to solve.

 I created some quick test data in Excel to demonstrate.

Imagine I don't already have the delta, and want to extrapolate it from a data set in the load script. 

The bottom chart in the first screenshot is the visual representation of the Delta /Change on C , per 'Group/Category'

Below is the same data in Qlik. 

In the table, using  the following code works in that context, but FAILS on the chart ( Top RIGHT) 

IF (ISNULL(ABOVE(SUM(SOLD))),SUM(SOLD)-0, Sum(SOLD)-ABOVE(SUM(SOLD)))

I would have expected the same values as the table ( as show on bottom right chart ) 

[Mark_Winter]

If I have understood what you're asking correctly, does this cover it?

[Don_Stocks_ · Accepted solution]

Hi,

Change the CALC DELTA expression to... 

aggr(IF(ISNULL(ABOVE(SUM(SOLD))),SUM(SOLD)-0,
Sum(SOLD)-ABOVE(SUM(SOLD))), CINEMA, DATE)

The problem was happening because the line chart was grouping by DATE and then listing the CINEMA. Therefore the value returned by the above function was pickup up the value above which was the value for a different CINEMA. If you change the line chart to a table you can see more clearly what was happening. 

[ForgotMyOldUsername]

@Mark_Winter   -  yes  - that's that's how it should look, I made the data sets such that it'd be obvious which was which when extrapolated. 🙂

@Don_Stocks_   - thanks for taking the time to explain, that makes more sense now -  the Qlik way of doing things takes a bit of getting used to over SQL , especially contextually ( differences in behaviour between tables and charts " ) 

I'll mark yous as the answer!

As an aside - I went back and read some more docs, and based on your answer, looked at the load script again.

In this example 

if (Previous(CINEMA)=CINEMA,SOLD-Previous(SOLD),SOLD) as 'DELTA_LOAD_SCRIPT'  

worked, but it's reliance on data order being correct on the inline load.   

This is maybe where I miss SQL and the group by / order by  / partition functions so you could do inline aggregates. 

In a use case where you were pulling in 1000's of rows, would you use a temp table to order/group and add aggregates / delta ? 

[Don_Stocks_]

Glad it worked. Yes, I would calculate the delta and/or add aggregated data fields in the load script if the value is being referenced often in expressions. Not to overcomplicate things, but sometimes you have to consider the possibility that certain dates aren't present in the data. In that case, a master calendar table will help to fill in those gaps.

Lastly, it's good to know about the concept of an "As Of" table: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Still, sometimes the above function is all you need (with some set analysis and aggregation in the expression).