topic Re: Previous period Value in QlikView

Previous period Value

aisolomatin — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I'm trying to get the previous value in a straight table.

I've already checked solution with BELOW(), but it requires users sorting table by date and it's doesn't fit, for us.

Here is the table example:

So, if somebody could help I really appreciate.

Thanks in advance, Andrey

devarasu07 — Fri, 30 Jun 2017 15:36:17 GMT

HI,

Try to use like below,

Above(total sum(Fact))

sunny_talwar — Fri, 30 Jun 2017 15:41:29 GMT

You can try this

=Aggr(Above(Sum(Fact)), Territory, SubTerritory, CurDate)

or this

=Aggr(Above(TOTAL Sum(Fact)), Territory, SubTerritory, CurDate)

As long as your load order for CurDate is ascending, this should work for you.

aisolomatin — Mon, 03 Jul 2017 08:54:38 GMT

Sunny, Hi!

Yes, BELOW() works fine if data is ordered by asc.

But if I change load order in script, then it doesn't work, even if I make Load Order by CurDate in script.

That's why I don't want to use BELOW().

Maybe ! there is more basic solution for previous period value, than using BELOW()?

aisolomatin — Mon, 03 Jul 2017 08:57:10 GMT

Hi, Devarasu!

Yes, BELOW() works fine if data is ordered by asc.

But if I change load order in script, then it doesn't work, even if I make Load Order by CurDate in script.

Maybe there is more basic solution for previous period value, than using BELOW()?

Anonymous — Mon, 03 Jul 2017 09:11:22 GMT

The Best way I can think of is when you use set analysis. This would solve the issues you would come across later stages in building your app.

aisolomatin — Mon, 03 Jul 2017 10:16:01 GMT

I've tried this way, but get stucked with issue:

if I add field 'CurDate' to Dimensions, each row in table became looks like this:

Do you have any ideas how to solve it?

sunny_talwar — Fri, 07 Jul 2017 01:08:24 GMT

This might be the best way to do it