topic Re: Compare the previous year Data in QlikView

Compare the previous year Data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

period	category	money	count	mm	PM
Currentperiod-2	CAT	50000	100	5000	10
CurrentPeriod-1	CAT	40000	120	6000	6.6
CurrentPeriod-0	CAT	90000	500	8000	11.25

Until MM the data is coming from directly table. PM definition is money/mm . Now i need to compare the PM from current period to previous time periods. So i need to populate the values only in CurrentPeriod-1 and CurrentPeriod-0 sections. how to write the expression dynamically .

APM= (6.6-10)/10*100

Thanks,

Kumar

Re: Compare the previous year Data

dplr-rn — Fri, 21 Sep 2018 20:44:38 GMT

Did you try above function?

taking PM expression with an example Sum(PM)

so your APM expression would be (Sum(PM) - above(Sum(PM)) )/above(Sum(PM))

see below article for more details

The Above Function

Re: Compare the previous year Data

Anonymous — Fri, 21 Sep 2018 21:12:51 GMT

SUM(MONEY)/SUM(MM)-ABOVE(SUM(MONEY)/SUM(MM))/ABOVE(SUM(MONEY)/SUM(MM))

Re: Compare the previous year Data

dplr-rn — Fri, 21 Sep 2018 21:41:08 GMT

Watch you brackets it should be (a-b)/b

right now its a-b/b

let me know if it works. Mark as answered it it does

Re: Compare the previous year Data

Anonymous — Mon, 24 Sep 2018 14:36:53 GMT

Ranjith,

If i don't have category column in main table view the formula works fine. Because in original data category column have different types like CAT1,CAT2. the formula is working for single dimension only. Thanks For your help !

Thanks,

Kumar

Re: Compare the previous year Data

dplr-rn — Mon, 24 Sep 2018 15:43:45 GMT

number of dimensions shouldnt matter.

are you using a pivot table where one of the dimensions have been pivoted to a column? if so use before function

Missing Manual - Before() and After()