topic Re: Calculation in pivot based on previous cells in QlikView

previous cells

Anonymous — Sun, 13 Dec 2015 07:09:50 GMT

Hello

i am attaching an excel sheet and qvw and required help on calcuating on these in qlikview

please require your help

Re: Calculation in pivot based on previous cells

Anonymous — Sun, 13 Dec 2015 10:09:52 GMT

any suggestions?

Re: Calculation in pivot based on previous cells

marcus_sommer — Sun, 13 Dec 2015 11:30:55 GMT

With inter-record functions like above/below/before/after could you access the rows/columns around a cell and to reference them per column() or their label might be also helpful. But in your case with several different metrics you will need for each column an own expression with set analysis conditions and by calculation like previous year against the current year you need both expressions in one, like:

sum({< Year = {2014}>} value) / sum({< Year = {2015}>} value)

- Marcus

Re: Calculation in pivot based on previous cells

Anonymous — Sun, 13 Dec 2015 11:32:26 GMT

Thanks Marcus

if possible could you please share me the app?

Re: Calculation in pivot based on previous cells

marcus_sommer — Sun, 13 Dec 2015 11:36:57 GMT

Currently I have only the PE available and have only looked at the excel-file.

- Marcus

Re: Calculation in pivot based on previous cells

Anonymous — Sun, 13 Dec 2015 11:40:19 GMT

ok thanks never mind Marcus

but its not giving me the results in qv

Re: Calculation in pivot based on previous cells

marcus_sommer — Sun, 13 Dec 2015 11:47:25 GMT

Yes, it couldn't work if you used the expression directly - they was derived generic. You need to adjust it to your datamodel and the real field-names - it seems that you used a combined field from years and a category which results in FW 2014, TV 2015 .... and I have no idea how your datamodel looks like.

- Marcus

Re: Calculation in pivot based on previous cells

Anonymous — Sun, 13 Dec 2015 12:16:54 GMT

Thanks again Marcus

i 've given the original fields but didnt turn up as per the requirement:(

Re: Calculation in pivot based on previous cells

Anonymous — Sun, 13 Dec 2015 12:53:29 GMT

any help?

Re: Calculation in pivot based on previous cells

sunny_talwar — Sun, 13 Dec 2015 13:01:44 GMT

Something like this?

Expression:

If(GARAGE = Above(GARAGE), (([YTD 2014 VS 2015]/Above([YTD 2014 VS 2015]) * 100) - 100), 0)

Where [YTD 2014 VS 2015] is the column label for the second last column (it is turned blue when you check the expression)

Re: previous cells

Anonymous — Sun, 13 Dec 2015 14:58:20 GMT

Thanks Sunny for your help

Re: Calculation in pivot based on previous cells

sunny_talwar — Sun, 13 Dec 2015 15:13:47 GMT

Not a problem, I am glad I was able to help