CODEWITHSUNDEEP
qlikview
user3894950
user3894950

Reputation: 1

Qlikview: Past Year data calculations

I have a scenario. In the sample table below, I need to show the sales by year… And for each year, I need to show the last yr and last 2nd year sales for that year.

For example in 2014,

Current Year = 2014 Sales
Last Year = 2013 Sales
Current Year = 2013 Sales
Last Year = 2012 Sales



|----------2013------------|---------2014-------------| 

| Last Year | Current Year | Last Year | Current Year |

Ive tried but when i nest them under a year dimension.. the calculations are not working.. is there a way around this, to come up with this kind of report format? our user is very particular in having such format..

many thanks for the help.

Upvotes: 0

Views: 5095

Answers (3)

Görkem Eren
Görkem Eren

Reputation: 1

You can solve this problem at the script side while loading data. So that you can compare year to date data with previous year with until corresponding month.

Transaction_Table:
 LOAD date,productID,amount
 FROM data.qvd;

concatenate

 Load AddYears(date,1) as date,productID,amount_1
 from data.qvd where date<=AddYears($(=max(date)),-1);

Data_Table:
 load distinct 
 date,
 month(date) as Month,
 year(date) as Year
resident Transaction_Table;

There will be two coloumns "amount" is current date's data and "amount_1" is previous year's same day data.

Create pivot chart put year to top and product to left and create two expressions. One for calculation of amount_1: previous term and one for amount: current term

You can name expressions: previous year label: =year-1 current year label: =year

Upvotes: 0

uzilan
uzilan

Reputation: 2624

I think this could be achieved using a pivot table. Here's an example.

Upvotes: 0

seebach
seebach

Reputation: 621

I'd simply hardcode all rows, and skip the year dimension:

Current Year
Sum({< Date = {">=$(=YearStart(min(Date),0"}*{">=$(=Addyears(max(Date),0)"} >} SalesAmount) 

Last Year:
Sum({< Date = {">=$(=YearStart(min(Date),-1"}*{">=$(=Addyears(max(Date),-1)"} >} SalesAmount)      

-2 Year:
Sum({< Date = {">=$(=YearStart(min(Date),-2"}*{">=$(=Addyears(max(Date),-2)"} >} SalesAmount)

Upvotes: 1

Related Questions