topic Re: Pivot Table with Different row and column Calculation in QlikView

Pivot Table with Different row and column Calculation

Anonymous — Mon, 27 Mar 2017 10:52:47 GMT

Dear All,

I would like to develop a finance pivot that is having different calculation on each row and column. A sample is attached.

There are 3 rows (dimension) as below:

1. COUNT(StaffID)

2. SUM(Revenue)

3. SUM(COGS)

Then there are 4 columns (expression) as below:

1. PrevMonth

2. CurrMonth

3. CurrMonthBudget

4. PrevYear

Currently I am unable to get the Pivot table. May I know is this Pivot table possible in Qlikview? Essentially the the figures that appear in dimension calculation depends on the expression.

Do let me know if more information would help in this problem I am facing. Thanks in advance!

Sincerely Yours,

Chin

Re: Pivot Table with Different row and column Calculation

sunny_talwar — Mon, 27 Mar 2017 11:30:48 GMT

May be see if this helps

How to Create a Profit and Loss Statement in QlikView

Re: Pivot Table with Different row and column Calculation

mato32188 — Mon, 27 Mar 2017 13:46:04 GMT

Hi Chee,

in Dimensions: Add calculated dimension: =ValueList('No. of Staff', 'Revenue', 'COGS')

in Expressions: Create 4 expressions

PrevMonth = if(ValueList('No. of Staff', 'Revenue', 'COGS')='No. of Staff', Count({<USE SET ANALYSIS TO GET PREV MONTH>}StaffID),

if(ValueList('No. of Staff', 'Revenue', 'COGS')='Revenue',

sum({<USE SET ANALYSIS TO GET PREV MONTH>}Revenue),

if(ValueList('No. of Staff', 'Revenue', 'COGS')='COGS',

sum({<USE SET ANALYSIS TO GET PREV MONTH>}COGS),null()

)

Replicate it and use right set analysis for another 3 expressions (CurrMonth,CurrMonthBudget,PrevYear).

Martin

Re: Pivot Table with Different row and column Calculation

Anonymous — Tue, 28 Mar 2017 03:47:06 GMT

Hi Talwar,

Thank you very much for the prompt response. Indeed the sample provided is very good. I will need sometime to digest the content. Will get back to this topic after i try it out.

Have a nice day ahead!

Re: Pivot Table with Different row and column Calculation

Anonymous — Tue, 28 Mar 2017 03:48:21 GMT

Hi Martin,

Thank you very much for the straight to the point direction. I will try to implement this in my chart. hope everything will be fine.

Have a nice day ahead!

Re: Pivot Table with Different row and column Calculation

Anonymous — Mon, 03 Apr 2017 08:35:52 GMT

Hi Martin,

May I know is it possible to assign the ValueList as a variable? because my ValueList is having more than 10 values.

Hope to hear from you soon. Thanks in advance!

Re: Pivot Table with Different row and column Calculation

mato32188 — Mon, 03 Apr 2017 08:59:40 GMT

Hi Chee,

of course. For example use Ctrl+V, create it and use across your app.

Martin

Re: Pivot Table with Different row and column Calculation

mato32188 — Mon, 03 Apr 2017 09:00:25 GMT

*mistake

Ctrl+Alt+V