Formula Previous Year

[qlikviewaf]

Hi,

i created this graph below, and i would like to have a line with the Margin of Previous year.

I created the expression as follow, but is not working: basically is the (Sales - Cost)  / Sales

Anyone can help on this?
Thanks

=(round(

(

num(((if(vExcludeInstrumentSales,sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedUSD),Sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'},Item_Category={$(vNoInstruments)}>}ExtendedUSD))/1000))*100, '#,')

-

num(((if(vExcludeInstrumentSales,sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedCostUSDADJ),Sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'},Item_Category={$(vNoInstruments)}>}ExtendedCostUSDADJ))/1000))*100, '#,')

)

/

num(((if(vExcludeInstrumentSales,sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedUSD),Sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'},Item_Category={$(vNoInstruments)}>}ExtendedUSD))/1000))*100, '#,')*100))

[fvelascog72]

Hi,

Try with this solution

Saludos

[qlikviewaf]

Works on your qvw, but what u did? if i copy to my origina qvw is not working anymore

[fvelascog72]

Hi,

I generate the expression with your requeriment: ([Sales] - [Cost])/[Sales]

And that is:

=

(

  if(vExcludeInstrumentSales,sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedUSD),Sum({<Item_Category={$(vNoInstruments)},Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedUSD))

-

  if(vExcludeInstrumentSales,sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedCostUSDADJ),Sum({<Item_Category={$(vNoInstruments)},Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedCostUSDADJ))

)

/

if(vExcludeInstrumentSales,sum({<Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedUSD),Sum({<Item_Category={$(vNoInstruments)},Year = {"$(=Max(Year)-1)"},Area-={'Area-not-available'}>}ExtendedUSD))

In number tab I used two decimals and show %.

Saludos.