Pivot Table Grouping for only existent values

[tommyl]

Hello experts,

I need your help about below issue:

My data is like:

company	fileName	activityType	orderSum	campaignSum
A1	F1	C1	10	50
A2	F2	C2	14	32
A3	F3	C3	22	67
A4	F4	C4	15	8
A3	F3	C3	20	4

I want to create a pivot table with:

• dimensions:
  • company,
  • fileName
  • activityType
• measures:
  • sum(aggr(sum(orderSum),company,fileName,activityType)
  • sum(aggr(sum(campaignSum),company,fileName,activityType)

But the result is far ahead of my need as:

For Company A the result is as attached

(Same for the other Companies with related sums)

But what i need is : 

company	fileName	activityType	orderSum	campaignSum
A1	F1	C1	10	50
A2	F2	C2	14	32
A3	F3	C3	42	71
A4	F4	C4	15	8

I dont want zero valued unnecassry, notrelated rows to be displayed. Could you please help me how to do this? 

Thank you,

Regards,

Tommy

[TimvB]

The desired table as you described is a straight table instead of a pivot table. You should add the following to the straight table:

• Dimensions:
  • Company,
  • FileName
  • ActivityType
• Measures:
  • sum(OrderSum)
  • sum(CampaignSum)

Hope it helps!

[tommyl]

Hello Tim,

Thank you for the response. I need to display data in groupings in case a company may have more than one fileName and activitycode.

Sorry my bad not to mention that, so straight table's not working in groupings i think. 

Thank you

Tommy

[TimvB]

Hi Tommy, 

A straight table might still be an option for you if you have grouped data, but this depends on the tasks and preferences of the final users. I provided a QVF (attached) and a screenshot with some examples how you can visualize your data in a (pivot) table without having null values. I hope you get some inspiration.

Tim

[tommyl]

Hello,

Thank you, i did it like that but the problem is, pivot displays every activitycode and filename for each partner, recurrently. I mean regardless of the filename or activitycode belongs or not belong to that partner. Do you have a clue?

Regards,

[sunny_talwar]

Did you try to uncheck 'Include zero values' under Add-ons -> Data handling

[tommyl]

Hello,

I saw that in one of your replies, and applied but it only hides zero valued totals.

Regards,

[sunny_talwar]

Can you show an image with and without 'Include zero values' to show what you get? And point out what the goal is?

[tommyl]

Hello,

Zero values are ommitted. It is ok but there is a situation of multiplication of data for each partner:

Sorry for the renamings(NDA),

Partner 2 has no file1 and no activity1 actually. Only partner1 has file1 and activity1. but in pivot it seems asif it has. 

Regards,

[sunny_talwar · Accepted solution]

It seems more of a data model issue to me...