Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

How to create dimension from excel date and how to compare previous vs current year sales


From my data sample, how can create a dimension (for example product_sales) that contains sales data?

The chart  expresions must be something like grouping for year,  sum of each product . Ialso need to create expresions that allow me calculate de difference between sales of the selected year respect to the previous one, as well as between the first year and the last year of the my data.

Thanks in advance for help

4 Replies

Hi Dude,


Hope ,you forgot to attach your input excel file so it is difficult to understand your requirement for us.

1) =Aggr(Sum(Sales)Year)

2) =Aggr(Sum(Sales)Product)

3) Sum(Sales) Current year vs previous year




example like below

Consider you have below script..



Load * Inline


  Customer, Sales, Year

  A, 100, 2012

  A, 120, 2013

  B, 90, 2012

  B, 110, 2013

  C, 150, 2012

  C, 110, 2013




You can show CY vs LY/PY sales growth as below


Create a Straight Table






2012 Sales

SUM({<Year = {2012}>}Sales)


2013 Sales

SUM({<Year = {2013}>}Sales)



SUM({<Year = {2013}>}Sales)/SUM({<Year = {2012}>}Sales)-1


Don't forget to change Number formatting of Growth to percentage from Properties = Number Tab



Sum( {<Year= Max(Sales Year)>}Sales) This will give last year sales

Sum( {<Year= Min(Sales Year)>}Sales) This will give First/Starting  Year sales

Please close the thread if you thought you got solution for your query

Contributor III
Contributor III

Hi Ramasaisaks!.
First of all,I must thank you for your reply!
Please, check that my sample file is included in my question (file name question.xlsx).

Hi Francis,


so sorry for my mistake, i didn't observe your attachment.


Actually i was unable to download attachments, Due to some restrictions in my office . sure i will try in my personal machine and will give reply ASAP.

Contributor III
Contributor III

Don't worry!
Really your interest for help
I advence the data
As you can see, i have 4 product fields, I need one dimension containig those fields. Done this, do the calculations indicated in my first post
Cod_empl Year Product_1 Product_2 Product_3 Product_4
1 2018 193 130 175 192
2 2018 125 110 142 165
3 2018 100 123 104 121
1 2017 129 162 111 137
2 2017 105 168 105 125
3 2017 191 166 196 163
1 2016 132 124 188 129
3 2016 227 196 133 206