topic Re: Pivot Table(With 2 Dimension) Expression in QlikView

Pivot Table(With 2 Dimension) Expression

Anonymous — Tue, 25 Apr 2017 03:29:15 GMT

Dear All,

Currently I have a Pivot Table which is having 2 dimensions. Kindly refer to the attached image.

I would like to write the expression that will calculate the results based on the value of the dimension 1 and dimension 2.

My Expression is as below:

=IF([Income Statement]='No. of Revenue Contributing Outlets' AND [Profit & Loss]='',

COUNT(Outlet),

IF([Income Statement]='Revenue' AND [Profit & Loss]='Revenue',

SUM(Revenue),

))

But somehow I cannot get the result for the [Income Statement]='No. of Revenue Contributing Outlets' AND [Profit & Loss]=''

I think this is possibly due to the [Profit & Loss] ='' is incorrect. Does anyone have any idea on what expression should i type?

Do let me know if further information is required.

Thanks in advance!

Re: Pivot Table(With 2 Dimension) Expression

rahulpawarb — Tue, 25 Apr 2017 05:59:01 GMT

Hello Chee Min Chin,

Hope you are doing well!

Please try below sample expression:

=If([Income Statement] = 'No. of Revenue Contributing Outlets' AND Len([Profit & Loss]) = 0,
     Count(Outlet),
  If([Income Statement]='Revenue' AND [Profit & Loss]='Revenue',
     SUM(Revenue),
  0))

Let me know if issue still exists.

Regards!

Rahul

Re: Pivot Table(With 2 Dimension) Expression

Anonymous — Tue, 25 Apr 2017 07:05:06 GMT

Dear Rahul,

Thank you for the quick reply.

But unfortunately it does not work. If I only put the following, it will work.

=If(Len([Profit & Loss]) = 0,

Count(Outlet),

But If I put the follwing, it will only show -.

=If([Income Statement] = 'No. of Revenue Contributing Outlets' AND Len([Profit & Loss]) = 0,

Count(Outlet),

What could be the issue or is there anything that I should take note of?

Thanks in advance!

Re: Pivot Table(With 2 Dimension) Expression

rahulpawarb — Tue, 25 Apr 2017 07:08:26 GMT

Could you please share application with sample data?

Regards!

Rahul

Re: Pivot Table(With 2 Dimension) Expression

Anonymous — Tue, 25 Apr 2017 07:42:48 GMT

Dear Rahul,

I've uploaded the qvw to sendspace due to huge file size.

I've reduced the data size.

You may refer to the first expression of the Pivot Chart.

Do let me know if there is anything else I can provide.

Thanks in advance!

Re: Pivot Table(With 2 Dimension) Expression

mayankraoka — Tue, 25 Apr 2017 09:53:43 GMT

Hi ,

Can you try the following .The application you shared we dont have have any data for 2016.

=If([Income Statement] = 'No.of Revenue Contributing Outlets' AND Len([Profit & Loss]) = 0,

Count(Outlet),

Regards,

Mayank

Re: Pivot Table(With 2 Dimension) Expression

Anonymous — Tue, 25 Apr 2017 10:07:55 GMT

Dear Mayank,

I'm afraid your suggestion has been suggested by Rahul before this, and turns out it does not work.

Dear Rahul and Mayank,

On top of that, I've tried to add another expression to see the combination of [Income Statement] and [Profit & Loss], seems like it is showing total blank. Kindly refer to the attached image.

I believe this is caused by my excel mapping, at which the 'No. of Revenue Contributing Outlets' are not mapped to any GL Accounts.

What I tried now is to purposely force it to map against a GL Account, just for the sake for showing my Pivot table correctly.

Hope there are better options beside what am I doing now.

Hope to hear from you guys soon.

Thanks in advance!