topic Put a calculation both in rows and columns in QlikView

Put a calculation both in rows and columns

Tue, 15 Dec 2009 21:33:57 GMT

Hi there,

I'm looking to present some financial key figures in a small pivot table with a calculated field both in row and column:

example

Actual Plan Variance

Revenue

Profit

Profitmargin%

Revenue and profit are values of a field called 'reportinglevel1),

Actual an plan are based on field called 'plan/actual indicator).

Variance is Actual - plan

Profitmargin% should be Profit / Revenue

Seems like a vary simple taks but i don't seem to be able to put calculations in both rows an columns.

Has anyone come across this problem and found a solution?

Thanks,

Wim

Put a calculation both in rows and columns

Wed, 16 Dec 2009 00:10:22 GMT

Not sure how this will work out but you would need to add in Revenue, Profit & ProfitMargin% as calculated dimensions. Then add Actual, Plan & Variance as expressions.

Try it & let me know how it goes.

Put a calculation both in rows and columns

johnw — Wed, 16 Dec 2009 05:54:55 GMT

Attached is one way to do it. Seems like it should be simple, but... no so much. The example doesn't use your fields. It's just an example I had lying around that demonstrates the technique.

Put a calculation both in rows and columns

Wed, 16 Dec 2009 15:39:15 GMT

Thanks,

Never used the 'pick' function before. It seems to me that the example only works if you use numbers (1,2,3..) in the value list. I f you were to use descriptive values like 'Revenue, Profit, ProfitMargin' then you should add If statements to determine the number within the value list, right?

Put a calculation both in rows and columns

johnw — Thu, 17 Dec 2009 02:34:48 GMT

You can still use pick, but you have to do pick(match()). And IF statements work too. Not sure which I would prefer at this point. Pick(match()) probably runs slightly faster, and uses less code. But IF statements are probably more clear, and therefore more maintainable. I've updated the example with both approaches.