topic Re: How to calculate the difference between count and previous row count ? in QlikView

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

bob654321 — Wed, 29 Jan 2020 00:06:34 GMT

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 ?

Re: How to calculate different between count and previous row count ?

Yevhenii_Senko — Tue, 28 Jan 2020 23:51:27 GMT

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.

Re: How to calculate different between count and previous row count ?

bob654321 — Wed, 29 Jan 2020 00:07:34 GMT

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

Re: How to calculate different between count and previous row count ?

Yevhenii_Senko — Wed, 29 Jan 2020 00:44:18 GMT

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..

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

Brett_Bleess — Mon, 24 Feb 2020 15:54:52 GMT

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