Showing Error in Pivot Table

[manpri7078]

Hi All.

I am making a pivot table to analyze some Data. Here is screen shot for one particular date :

Date	Ship Customer Name	Quantity	Price	Revenue	Error Status
20-Jun-17	P C MALPANI & COMPANY-STOCKIST	0.594	154.100	91535	1
	B R INDUSTRIES LIMITED-STOCKIST	2.244			1
	SHRI TAPODHANI ALUMINIUM TRADING COMPANY-STOCKIST	1.069	155.100	165802	1
	PVC DECORATORS-STOCKIST	2.264	154.100	348882	1

In this Pivot Table Row No 2 representing Customer name as BR Industries Limited-Stockist is showing Price as blank because 2.244 is the sum total of one or more quantity but the price for the same is different which should be same as per my company's policy. I want to track where price is different so that price column be not populated and hence I can concentrate on those types of line items.

Now I want the last column to show 1 or 0 depending upon the situation whether price is blank or contains some figure.

Secondly I want to filter the last column to select either 0 or 1 to have a glance on the desired situation.

I am trying to define expression in the last column in the form of if(column(3)='',0,1), but it is not giving me desired output as can be seen from the table.

Please advise me how to build such Pivot Table as I have not made Pivot Table before and I am absolutely new in this area.

Regards,

Manish Prasad

[sunny_talwar]

Not entirely sure what sorting requirement do we have here?

[vishsaggi]

In the attached document there are some 0's in the status column which is a calculated expression. But when we use this in our sort expression its not sorting. He wants all 0's to be on top and 1's after.

[sunny_talwar · Accepted solution]

The only way I was able to get this done is by adding another dimension to the table (called Key)

We can hide Key using Pivot Column Width to Zero

Key is created like this in the script

Table1:

LOAD AutoNumber([Excise Invoice Date]&'|'&[Alloy Temper]&'|'&[Composition]&'|'&[Ship Customer Name]) as Key,

[Alloy Temper],

     [Bill Customer Name],

     [Bill Customer No],

     Composition,

     [Cst Amt],

     [Customer Class Code],

Basically took all the dimensions from the chart to create a unique value for each row.

[vishsaggi]

Thanks Sunny, lets see if that works for Manish.

[manpri7078]

Dear Sunny,

Once again thanks for your suggestion. You are able to do in Pivot Table. Bravo.

But can the display presentation be like usually we have for Pivot Table. The Table is looking like straight table but I want the display like Pivot Table. If the Key dimension is not there then it is looking like Pivot though.

If possible then please guide me otherwise It is OK. The same presentation I was able to do in straight table as in straight table sorting can be done on expressions easily.

I was trying to achieve the same result in the form of Pivot Table Presentation.

If possible then please guide me.

Waiting for your reply.

If not possible then I will mark it correct after getting your reply.

Regards

Manish Prasad

[manpri7078]

It does works for me but the table is looking like straight table and not the way I was trying to make. But anyway thanks for helping me. I was able to achieve the same though Straight Table, but I was trying to do the same through Pivot Table concept and hence was my query.

Regards,

Manish Prasad

[sunny_talwar]

Sorting has been always an issue within pivot table.... the problem is that how do you imagine to see all the 0's at the top while still maintaining the groupings. For example... lets look at this

How do you imagine to see 10-Dec-16 and 12-May-17 at the if there are more entries for 10-Dec-16.... even within 10-Dec-16, you have two different Alloy Tempers.... so, I guess either you can bring all the 0's to the top or you can maintain all the groupings (the pivot table view).

Does that make sense? or am I missing what the final output you are looking to get?

[manpri7078]

Dear Sunny,

I fully agree with you. What I want can not be done through Pivot Table view. So I have decided to do it through straight table itself which is the right one for my output.

But anyway thanks for your support and guidance and the way you managed to do the same in a pivot table is outstanding.

Your guidance has as usual always helped me.

Regards,

Manish Prasad