Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
aisolomatin
Contributor III
Contributor III

Previous period Value

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:

   

TerritorySubTerritoryCurDateFactExpected
SouthDistrict101.01.2017100-
SouthDistrict101.02.201795100
SouthDistrict101.03.20179995
SouthDistrict101.04.20179199
SouthDistrict201.01.20178591
SouthDistrict201.02.20178485
SouthDistrict201.03.20177584
SouthDistrict201.04.20179775

So, if somebody could help I really appreciate.

Thanks in advance, Andrey

7 Replies
devarasu07
Master II
Master II

HI,

Try to use like below,

Above(total sum(Fact))

Capture.JPG

sunny_talwar

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
Contributor III
Contributor III
Author

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.

Prev_val.png

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

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

aisolomatin
Contributor III
Contributor III
Author

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()?

willbleksem
Contributor III
Contributor III

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.

https://community.qlik.com/docs/DOC-4313

aisolomatin
Contributor III
Contributor III
Author

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:

Prev_val_2.png

Do you have any ideas how to solve it?

sunny_talwar

This might be the best way to do it

The As-Of Table