Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivot Table(With 2 Dimension) Expression

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!

6 Replies
rahulpawarb
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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!

rahulpawarb
Specialist III
Specialist III

Could you please share application with sample data?

Regards!

Rahul

Anonymous
Not applicable
Author

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!

mayankraoka
Specialist
Specialist

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

Anonymous
Not applicable
Author

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!