How to calculate the difference between count and previous row count ?

[bob654321]

I need to calculate the difference between row count and previous row count that was overwritten later ?

For example , my data looks like this below

DateTime                     row count

01/28/2019                          54

01/27/2019                          43

When I refresh the dashboard, the row count for 01/28(which is today) gets updated, so the new count looks like this below

DateTime                     row count

01/28/2019                          72

01/27/2019                          43

How can I calculate the difference between the row count generated from the current run  and row count generated from the previous run ?

[Yevhenii_Senko]

Hi,
You may store data from each run into qvd (add or replace).
Then load this data and make calculation between stored and new rows.

[bob654321]

It's just one qvd that gets overwritten again and again. So, how to  store the previous value that was already updated ?

[Yevhenii_Senko]

If you don't need a history then it will update on each reload.

e.g.

IF IsNull(filetime('RowCountPrior.qvd ')) THEN // Necessary for the first run when no qvd generated

RowCountPrior:

LOAD * Inline [

DateTime as DateTimePrior,

RowCount as RowCountPrior

FROM source;

Store RowCountPrior into RowCountPrior.qvd (qvd);

Drop Table RowCountPrior;

RowCountTable:

LOAD

DateTime ,

RowCount,

'0' as RowCountDiff

FROM source;

ELSE

Map_PriorRowCount:

Mapping Load

DateTimePrior,

RowCountPrior

FROM RowCountPrior.qvd (qvd);

RowCountTable:

LOAD

DateTime ,

RowCount,

RowCount - ApplyMap('Map_PriorRowCount', DateTime ) as RowCountDiff

FROM source;

RowCountPrior:

LOAD * Inline [

DateTime as DateTimePrior,

RowCount as RowCountPrior

FROM source;

Store RowCountPrior into RowCountPrior.qvd (qvd);

Drop Table RowCountPrior;

END IF

I think you may skip the last step (RowCountPrior) and store into qvd from RowCountTable

It depends on requirements..

[Brett_Bleess]

Bob, did the final post get you a working solution for your use case?  If so, do not forget to return to your thread and on that post, use the Accept as Solution button to mark it to give credit to the poster for the help and to let other Members know that did work for you.  The only other idea I had was to perhaps use variables, but the trick is if you are loading things an inconsistent number of times, that would not work, believe the other method proposed would be the way to go in that case.

For future reference, you can also check the Design Blog area of the Community to see if you can find any ideas out there:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett