topic Re: last year Sales in QlikView

last year Sales

Thu, 11 Feb 2016 11:06:43 GMT

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 ()

Re: last year Sales

marcus_sommer — Thu, 11 Feb 2016 13:27:28 GMT

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

Re: last year Sales

avinashelite — Thu, 11 Feb 2016 13:33:20 GMT

check this thread

QlikView App: Set Analysis - Prior Period Comparison

Year-over-Year Comparisons

Re: last year Sales

Thu, 11 Feb 2016 15:30:36 GMT

thank you for you reply.

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

Re: last year Sales

marcus_sommer — Thu, 11 Feb 2016 16:23:28 GMT

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

Re: last year Sales

Thu, 11 Feb 2016 16:44:46 GMT

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 .

Re: last year Sales

marcus_sommer — Thu, 11 Feb 2016 17:40:14 GMT

Please provide a small sample app.

- Marcus

Re: last year Sales

Tue, 16 Feb 2016 09:59:36 GMT

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

Re: last year Sales

marcus_sommer — Tue, 16 Feb 2016 11:22:36 GMT

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

Re: last year Sales

Anonymous — Tue, 16 Feb 2016 11:33:26 GMT

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

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

TAKE YOUR FIELD NAME

Re: last year Sales

Tue, 16 Feb 2016 12:24:17 GMT

Thank you so much for your reply now it works, i should remove the year dimension from the table. but now the year is not visible to the end user , How can i add it in the table labels??

Thank you for advices about dummy application.

Re: last year Sales

marcus_sommer — Tue, 16 Feb 2016 14:15:53 GMT

You could use an expression as label like:

= 'YourExpressionName ' & max(year)

= 'YourExpressionName ' & max(year)-1

- Marcus

Re: last year Sales

Tue, 16 Feb 2016 17:43:16 GMT

Thank you so mych.