Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
slaheddinekeyru
Partner
Partner

Sales of Year and Year-1

Hello,

I'll be very gratefull if you ca help me to get this result :

For each Entity I want to get the value of the year. The expression to use will be (sum(Sales)). But how can I get the Sales for N-1 by Entity ?

YearEntityNN-1
1996A                  354                       -  
1996B                  314                       -  
1996C                  429                       -  
1996E                  280                       -  
1996F                  485                       -  
1997A                  354                   354
1997B                  318                   314
1997C                  437                   429
1997D                  494                       -  
1997F                  492                   485
1998A                  374                   354
1998B                  319                   318
1998C                  448                   437
1998D                  491                   494
1998F                  520                   492
1999A                  359                   374
1999B                  321                   319
1999C                  464                   448
1999D                  495                   491
1999E                  309                       -  
1999F                  521                   520

Thank you very much for all your answers

1 Solution

Accepted Solutions
sunny_talwar

You can sort in Aggr() if you are using QV 12 or above or Qlik Sense, using this expression


Aggr(Above(Sum({<Year>}Sales)), Entity, (Year, (NUMERIC)))

View solution in original post

7 Replies
sergio0592
Specialist III
Specialist III

Just try with : above(TOTAL sum(N),6)

slaheddinekeyru
Partner
Partner
Author

Thank You Jean-Baptiste,

The problem that your solution supposes that we have the same number of entities in each year. But in reality it's not.

That's why I avoid using the Above function.

sunny_talwar

sunny_talwar

Or try this

Aggr(Above(Sum({<Year>}Sales)), Entity, Year)

slaheddinekeyru
Partner
Partner
Author

Thank You Sunny for your answer,

If we can just sort fields into the aggr it will be ok. But the result I get depends on the loading sort. I tried to sort data by Entity, Year asc but without success.

sunny_talwar

You can sort in Aggr() if you are using QV 12 or above or Qlik Sense, using this expression


Aggr(Above(Sum({<Year>}Sales)), Entity, (Year, (NUMERIC)))

View solution in original post

slaheddinekeyru
Partner
Partner
Author

Thank you very much Sunny !

You are simply incredible