Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fabianaiglesias
Contributor

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Cumulative current and previous year comparison

Changing vPreviousYear to this =((Max(Year)-1)) seem to have worked

Capture.PNG

35 Replies
MVP
MVP

Re: Cumulative current and previous year comparison

May be this

RangeSum(Above($(vPreviousSales), 12, RowNo()))

fabianaiglesias
Contributor

Re: Cumulative current and previous year comparison

I tried but it still doesn´t work

fabianaiglesias
Contributor

Re: Cumulative current and previous year comparison

Sunny

I´m attaching the qvs file for you to check it

I´ll really appreciate your help!

xufei123
Valued Contributor

Re: Cumulative current and previous year comparison

Will this work for you?

vishweshwarisun
Contributor

Re: Cumulative current and previous year comparison

try this expression for current year

=sum({<YEAR={$(=max(YEAR))}>}Sales)


try this expression for previous year

=sum({<YEAR={$(=max(YEAR)-1)}>}Sales)

MVP
MVP

Re: Cumulative current and previous year comparison

Changing vPreviousYear to this =((Max(Year)-1)) seem to have worked

Capture.PNG

fabianaiglesias
Contributor

Re: Cumulative current and previous year comparison

Hi

I tried that expression at the beguinning, but it didn´t worked

MVP
MVP

Re: Cumulative current and previous year comparison

Did you look at the attached qvf below?

fabianaiglesias
Contributor

Re: Cumulative current and previous year comparison

Hi Sunny,

I´m checking the attached file and it does work, but I don´t know why is not working in my complete file.

Give me a moment to complete the checking and I´ll give you an answer

Thanks a lot!

Community Browser