topic Re: Formula Previous Year in QlikView

Formula Previous Year

qlikviewaf — Fri, 15 Sep 2017 07:54:52 GMT

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, '#,')

)

Re: Formula Previous Year

fvelascog72 — Fri, 15 Sep 2017 08:18:59 GMT

Hi,

Try with this solution

Saludos

Re: Formula Previous Year

qlikviewaf — Fri, 15 Sep 2017 13:07:19 GMT

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

Re: Formula Previous Year

fvelascog72 — Mon, 18 Sep 2017 05:34:18 GMT

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

)

In number tab I used two decimals and show %.

Saludos.