topic Re: Pivot table- calculated columns in QlikView

Pivot table- calculated columns

aarohipatel — Thu, 01 Aug 2019 01:36:53 GMT

Hi,

I have an application wherein I am using Pivot table with the directly available fields. But, I need some additional calculated columns. Attached is the sample data and following are the new columns I want in the table(highlighted in yellow):

In the excel file attached for sample data, I have added a sheet 'Results expecting' where in the calculation of the new columns is available.

Only for the latest Year Qtr , I need month wise.

Could you please advise on how to achieve the new columns?

Thank you

Re: Pivot table- calculated columns

aarohipatel — Thu, 01 Aug 2019 13:58:57 GMT

@sunny_talwar Could you please provide your inputs and/or possible solution for my requirement?

Thank you!

Re: Pivot table- calculated columns

sunny_talwar — Thu, 01 Aug 2019 17:49:41 GMT

Check attached

Re: Pivot table- calculated columns

aarohipatel — Thu, 01 Aug 2019 21:56:46 GMT

Hi Sunny,

Thank you very much for the solution you provided. It is exactly what I need.

When I tried to implement the same in one of the test application, I am not getting the Pivot table working. Could you please take a look at it (attached) an let me know where am I going wrong?

You have used the following expression in sort- What is 1E10?

(YEAR * 10000),
-Rank(YEARQTR),
Dim/100,
YEARMONTH/1E10
)

One additional question, I have added a pivot chart 'Sum' (CH02) in which I am grouping 2 expressions. When I move the YEARQTR field position to horizontal, the grouping symbol is missing. But when I keep YEARQTR vertical, grouping is working fine(CH04). Could you please advise on this as well?

Thank you much!

Re: Pivot table- calculated columns

sunny_talwar — Fri, 02 Aug 2019 16:45:58 GMT

Pivot table? Why a pivot table?

IE10 = 10,000,000,000 (1 followed by 10 zeros)

For grouping, you need to add MONTH as the third dimension

Re: Pivot table- calculated columns

aarohipatel — Fri, 02 Aug 2019 17:09:57 GMT

Hi Sunny,

Thank you for responding. I did not get the answer for the following:

In the attachment that I have,

1. Chart number 03: I tried to implement the same as you but it is not working. Can you please let me know where am I going wrong?

2. Chart Number 04 is working fine but when I change the YearQtr from Horizontal to vertical (which is Chart 02), the expression grouping symbol is missing and I am trying to get solution for that.

(Difference between chart 04 and 05 is position of the YEARQTR field causing the cyclic group symbol to miss.

Please advise.

Thank you much!