last year Sales

[#1]

Hi,

I want to calculate the sales of the year before the year selected, so i used those formulas:

sales year N :       =  Sum(TF_TRANSACTION.MONTANT_REALISE)

budget year N :  =  Sum(TF_TRANSACTION.MONTANT_BUDGET)

sales year N-1 :   =Sum({$<ANNEE={$(=Max(ANNEE)-1)}>}TF_TRANSACTION.MONTANT_REALISE)

They give me the correct values but sales year N-1 do not display as i want. For exemple i choose YEAR=2014 once i add sales N-1 formula it appears on 2013 , it should appear on 2014

please see the screenshots attached ()

[marcus_sommer]

You need to include the set analysis in each expression:

Sum({$<ANNEE={$(=Max(ANNEE))}>}TF_TRANSACTION.MONTANT_REALISE) // current/selected year

Sum({$<ANNEE={$(=Max(ANNEE))}>}TF_TRANSACTION.MONTANT_BUDGET) // current/selected year

Sum({$<ANNEE={$(=Max(ANNEE)-1)}>}TF_TRANSACTION.MONTANT_REALISE) // previous year to above one

- Marcus

[avinashelite]

check this thread

QlikView App: Set Analysis - Prior Period Comparison

Year-over-Year Comparisons

[#4]

thank you for you reply.

i inculde set analysis in experssions of all indicators but i still having the same issue .

[marcus_sommer]

If you don't apply a selection on year it should work. Do you need these selections to go back further then all expressions which don't display the current year needs the 1 as set identifier to ignore the selection, like:

Sum({$<ANNEE={$(=Max(ANNEE))}>}TF_TRANSACTION.MONTANT_REALISE) // current/selected year

Sum({$<ANNEE={$(=Max(ANNEE))}>}TF_TRANSACTION.MONTANT_BUDGET) // current/selected year

Sum({1$<ANNEE={$(=Max(ANNEE)-1)}>}TF_TRANSACTION.MONTANT_REALISE) // previous year to above one

- Marcus

[#6]

thank you so much i get your point.

when i add 1 as set identifier it gives me the correct form, for a selected year i have : sales, budget, sales n-1 but the sales n-1 is empty .

[marcus_sommer]

Please provide a small sample app.

- Marcus

[#8]

Please have a look at the screenshot, its the result i get with

Sum({$<ANNEE={$(=Max(ANNEE))}>}TF_TRANSACTION.MONTANT_REALISE)

Sum({1$<ANNEE={$(=Max(ANNEE)-1)}>}TF_TRANSACTION.MONTANT_REALISE)

once i add 1 it gives empty sales fOR N-1

sorry , i can't provide app because im working with prod database

[marcus_sommer · Accepted solution]

Remove the year-dimension from your table and make sure that your further selections (Code_CateG... and mayby others) didn't exclude values for the previous year.

You are right not to post prod. data which are very probably confidential but with quite less efforts you could adjust them: Preparing examples for Upload - Reduction and Data Scrambling. But better as this is it most often to build a small dummy-application with a few records in inline-tables which are wide enough to demonstrate the issue but didn't confuse with the whole datamodel.

- Marcus

[Anonymous]

sum({<YEAR={$(=Max(YEAR))}>}SALES)

sum({<YEAR={$(=Max(YEAR)-1)}>}SALES)

TAKE YOUR FIELD NAME