Pivot Table with Different row and column Calculation

[Anonymous]

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

[sunny_talwar]

May be see if this helps

How to Create a Profit and Loss Statement in QlikView

[mato32188]

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

BR

Martin

[Anonymous]

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!

[Anonymous]

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!

[Anonymous]

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!

[mato32188]

Hi Chee,

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

BR

Martin

[mato32188]

*mistake

Ctrl+Alt+V