Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Cumulative current and previous year comparison

HI,

I have these data ant I need to compare current and previous year sales and cumulative current and previous year sales:

      

YearMonthcurrent SalesPrevious salesSales evolutioncurrent Cumulative SalesPrevious cumulative salesprevious Sales evolution
2016jan.$ 3.210.255,67$ 2.916.318,75 $ 3.210.255,67$ 2.916.318,75
2016feb.$ 2.416.694,16$ 2.619.870,73 $ 5.626.949,83$ 5.536.189,48
2016mar.$ 3.136.653,17$ 3.427.868,53 $ 8.763.603,00$ 8.964.058,01
2016apr.$ 3.014.915,70$ 2.970.871,88 $ 11.778.518,70$ 11.934.929,89
2016may.$ 3.224.530,82$ 2.936.163,16 $ 15.003.049,52$ 14.871.093,05
2016jun.$ 3.122.416,11$ 2.879.275,88 $ 18.125.465,63$ 17.750.368,93
2016jul.$ 3.820.239,27$ 3.237.616,84 $ 21.945.704,90$ 20.987.985,77
2016aug.$ 3.057.424,10$ 3.519.914,18 $ 25.003.129,00$ 24.507.899,95
2016sept.$ 3.191.870,08$ 3.790.792,88 $ 28.194.999,08$ 28.298.692,83
2016oct.$ 3.233.443,83$ 3.234.116,17 $ 31.428.442,91$ 31.532.809,00
2016nov.$ 2.600.200,37$ 2.815.782,99 $ 34.028.643,28$ 34.348.591,99
2016dic.$ 3.014.417,02$ 2.915.944,48 $ 37.043.060,30$ 37.264.536,47
2017jan.$ 2.655.247,31$ 3.210.255,6717,29%$ 2.655.247,31$ 3.210.255,6717,29%
2017feb.$ 3.012.526,90$ 2.416.694,16-24,65%$ 5.667.774,21$ 5.626.949,83-0,73%
2017mar.$ 3.291.494,73$ 3.136.653,17-4,94%$ 8.959.268,94$ 8.763.603,00-2,23%
2017apr.$ 3.100.229,69$ 3.014.915,70-2,83%$ 12.059.498,63$ 11.778.518,70-2,39%
2017may.$ 3.801.179,75$ 3.224.530,82-17,88%$ 15.860.678,38$ 15.003.049,52-5,72%
2017jun.$ 3.302.255,26$ 3.122.416,11-5,76%$ 19.162.933,64$ 18.125.465,63-5,72%
2017jul.$ 3.104.700,53$ 3.820.239,2718,73%$ 22.267.634,17$ 21.945.704,90-1,47%
2017aug.$ 3.977.982,00$ 3.057.424,10-30,11%$ 26.245.616,17$ 25.003.129,00-4,97%
2017sept.$ 3.211.017,79$ 3.191.870,08-0,60%$ 29.456.633,96$ 28.194.999,08-4,47%
2017oct.$ 3.705.129,89$ 3.233.443,83-14,59%$ 33.161.763,85$ 31.428.442,91-5,52%
2017nov.$ 3.247.800,47$ 2.600.200,37-24,91%$ 36.409.564,32$ 34.028.643,28-7,00%
2017dic.$ 3.351.796,31$ 3.014.417,02-11,19%$ 39.761.360,63$ 37.043.060,30-7,34%

I used this scrip:

vCurrentYear = Max(Year)

vPreviousYear = Max(Year-1)

vCurrentSales =           sum({<Year={$(vCurrentYear)}>}Sales)

vPreviousSales =           sum({<Year={$(vPreviousYear)}>}Sales)

vSalesEvolution = 1 - $(vCurrentSales)/$(vPreviousSales)

vCumSales =               RangeSum(Above($(vCurrentSales), 0, RowNo()))

vCumPreviousSales =   RangeSum(Above($(vPreviousSales), 0, RowNo()))

vCumSalesEvolution = 1 - $(vCurrentSales)/$(vPreviousSales)

I have these results for current sales:

But I can have the results for cumulative sales:

Could someone help me please?

35 Replies
Anonymous
Not applicable
Author

Sunny, I don´t found the mistake.

Despite in the qvf file you send me is working, in the complete model is not.

The data in the complete model is confidential, so I can´t share it in the community...I´m stuck.

Do you know a way to solve this problem?

Anonymous
Not applicable
Author

Thanks for your answer, but I need the sales be cumulative. I mean, not the previous year sales but the cumulative previous year sales

NZFei
Partner - Specialist
Partner - Specialist

Are you sure in the complete model, the two dimensions are year and month and they have been sorted?

sunny_talwar

Two questions

1) Do you have date and time related fields where you might be making selection?

2) You did try to change your vPreviousYear variable, right? I just want to make sure that you didn't mind reading it.

Anonymous
Not applicable
Author

Yes Fei, I´m pretty sure.

In fact, the qvf file I attached previously has a part of the original script and tha table and formulas were copied from it (I tested it with the whole script previously)

Anonymous
Not applicable
Author

2 answers:

1) I have the, but not in the page where I have these variables

2) I replace my old variable by vAño=((Max(Año)-1))

(spanish model)

adj29block
Contributor III
Contributor III

Hello,
I propose the following way using RangeSum and Above in a crosstab, with a visualization in a bar chart.
Dimension: Year and Month
Measure: RangeSum (Above (Sum (Sales), 0, rowno ()))

Important:
I recovered your data and modified some of them.
Cumulative.PNG

I hope this will help you.

J-B C

adj29block
Contributor III
Contributor III

You can have an explanation on the site Accumulation in Qlik Sense | Qlik Community

Have a good day

J-B C

sunny_talwar

For one, are you making selections in any of those fields while trying to viewing this chart?

Anonymous
Not applicable
Author

No I´m not making any selection in filter fields

It´s not a chart but a table