Solved: Re: Showing Error in Pivot Table - Qlik Community

manpri7078 · ‎2017-08-14

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 · ‎2017-08-15

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.

View solution in original post

vishsaggi · ‎2017-08-14

May be try this?

= IF(Len(Trim(Revenue)) = 0, 0, 1)

OR

= IF(ISNULL(Revenue) = -1, 0, 1)

manpri7078 · ‎2017-08-14

Hi Vishwarath,

That was helpful and I am able to see the desired output. I also want to filter on that Error Status Column to show either 0 or 1. Is this possible ?

Regards,

Manish Prasad

vishsaggi · ‎2017-08-14

Add this in your script and see.

IF(Len(Trim(Revenue)) = 0, 0, 1) AS ErrorStatus

manpri7078 · ‎2017-08-14

Hi Vishwarath,

I am calculating Revenue in that Pivot Chart, so I can do something in the front end only. It can't be defined in the load script.

Regards,

Manish Prasad

vishsaggi · ‎2017-08-14

So where do you want the filter column to be? Sorry i quite did not get you? Can you share some sample and what you are expecting?

manpri7078 · ‎2017-08-14

Hi Vishwarath,

After making Pivot Table as desired, Now I want the Pivot Table to show only those rows where the column of Error Status is showing 0 and hide all other rows where it is showing as 1.

Is this possible ?

Regards

Manish Prasad

vishsaggi · ‎2017-08-14

You have to write a calculated dimensions for that. If you can share some sample data i can look into it.

vishsaggi · ‎2017-08-14

Something like this? I am not sure your expressions but you can try like

= IF(Aggr(Sum(Revenue),Date, ShipCustomerName) = 0, Date)

= IF(Aggr(Sum(Revenue),Date, ShipCustomerName) = 0, ShipCustomerName)

And your regular expressions.

manpri7078 · ‎2017-08-14

Hi Vishwarath,

I am attaching sample document.

I want to sort by last column expression so that all rows with value equal to 0 comes at the top.

Please guide me how it can be made possible.

Regards,

Manish Prasad