Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Most recent year of data - Expression/Set analysis


I am tring to get the right expression to get the sales of a customer from their most recent year of sales. I tried using an AGGR expression with a FirstSortedValue expression but I must be way off cuz nothing returns:

firstsortedvalue(aggr(sum(Sales),Customer), -Year) :

Data:
CustomerYearSales
A201425
A201450
A201375
B2012100
B2011125
B2011150
C2013175
C2013200
C2010225
C2009250
DESIRED Results:
Customer Latest YearTotal Sales
A201475
B2012100
C2013375
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Unfortunately, this problem cannot be solved with Set Analysis (at least, not directly). The reason being - Set Analysis condition is calculated only once per chart, and therefore it cannot be sensitive to your dimension values.

The solution can be built using a bit of scripting. You can determine in the script what year is the latest year for each Customer. Ideally, you can calculate a new field _Last_Year_Flag in your fact table that will be assigned 1 for all the transactions that belong to the last year for the given customer. With a flag like this, the Set Analysis expression becomes independent of your dimension, and therefore it becomes valid:

Dimension: Customer

Expressions:

     Latest Year = Max(Year)

     Sales = sum( {<_Last_Year_Flag={1}>} Sales)

cheers,

Oleg Troyansky

Check out my book QlikView Your Business - now available to pre-order on Amazon!

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

View solution in original post

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Unfortunately, this problem cannot be solved with Set Analysis (at least, not directly). The reason being - Set Analysis condition is calculated only once per chart, and therefore it cannot be sensitive to your dimension values.

The solution can be built using a bit of scripting. You can determine in the script what year is the latest year for each Customer. Ideally, you can calculate a new field _Last_Year_Flag in your fact table that will be assigned 1 for all the transactions that belong to the last year for the given customer. With a flag like this, the Set Analysis expression becomes independent of your dimension, and therefore it becomes valid:

Dimension: Customer

Expressions:

     Latest Year = Max(Year)

     Sales = sum( {<_Last_Year_Flag={1}>} Sales)

cheers,

Oleg Troyansky

Check out my book QlikView Your Business - now available to pre-order on Amazon!

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

View solution in original post

Highlighted
Master III
Master III

Hello Zab,

I agree with Oleg and he had given you a good solution.

Well i always believe in playing with script that in UI since it minimizes the load on object and hence gives good response time .

I will recommend you the below way of implementing your need.

Well you can go for any it is your way.

PFA the sample file

Highlighted
Specialist III
Specialist III

If you want to solve the problem with scripting only, the following script works

A:

load * Inline [

Customer,Year,Sales

A,2014,25

A,2014,50

A,2013,75

B,2012,100

B,2011,125

B,2011,150

C,2013,175

C,2013,200

C,2010,225

C,2009,250

];

B:

NoConcatenate LOAD Customer,Year

,Sum(Sales) as Sales1

Resident A group by Customer, Year;

Drop  Table A;

C:

NoConcatenate Load Customer

,FirstSortedValue(Year,-Year) as MaxYear

,FirstSortedValue(Sales1,-Year) as MaxSales

Resident B Group By Customer;

Drop Table B;

Highlighted
Creator
Creator

Thanks Oleg!

If you have a minute can you explain this, not sure I totally understand : The reason being - Set Analysis condition is calculated only once per chart, and therefore it cannot be sensitive to your dimension values.

Steve

Highlighted
MVP & Luminary
MVP & Luminary

In your initial question, you wanted to calculate sales for the last year for each customer. That means that every line of the chart should be calculated with a Set Analysis condition that is dependent on the dimension value (Customer) at the same line.

Since Set Analysis condition is only calculated once, before the chart is being produced, it cannot contain any conditions that are dependent on the dimension values.

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

Highlighted
Creator
Creator

Thanks!

Steve

Highlighted
Creator
Creator

Thanks!