topic Re: Showing Error in Pivot Table in QlikView

Showing Error in Pivot Table

manpri7078 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 18:40:13 GMT

May be try this?

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

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

Re: Showing Error in Pivot Table

manpri7078 — Mon, 14 Aug 2017 18:45:30 GMT

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

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 18:53:43 GMT

Add this in your script and see.

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

Re: Showing Error in Pivot Table

manpri7078 — Mon, 14 Aug 2017 19:03:23 GMT

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

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 19:07:38 GMT

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?

Re: Showing Error in Pivot Table

manpri7078 — Mon, 14 Aug 2017 19:12:11 GMT

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

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 19:21:43 GMT

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

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 19:37:23 GMT

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.

Re: Showing Error in Pivot Table

manpri7078 — Mon, 14 Aug 2017 19:43:34 GMT

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

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 19:50:30 GMT

You mean you want to sort this by like below?

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 19:51:12 GMT

Check attached?

Re: Showing Error in Pivot Table

manpri7078 — Mon, 14 Aug 2017 20:02:51 GMT

Hi Vishwarath,

Initially I had uploaded my main document by mistake. On realizing it I uploaded sample document after modifying my post. In my sample document attached I had made the selection in Date for 20. Deselect the date list box. Now my pivot table is showing me data for the month of Jun.

Now I want that all those rows where status is showing 0 comes at the top.

Hope I am more clear now.

Regards,
Manish Prasad

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 20:22:22 GMT

Check this?

Re: Showing Error in Pivot Table

manpri7078 — Mon, 14 Aug 2017 20:28:53 GMT

No, It is not showing me desired results.

First of all deselect the Date list Box so as to show all data from 1 to 30.

Now I want the pivot table to show at the top all rows with 0 as last column and then all those rows with value 1 so that at one glance I can see all those rows which are having value as 0 in the last column at the very top.

I hope I am clear in my query

Regards

Re: Showing Error in Pivot Table

vishsaggi — Mon, 14 Aug 2017 20:39:59 GMT

Oh sorry I miss read it. I will look into it and get back.

Re: Showing Error in Pivot Table

vishsaggi — Tue, 15 Aug 2017 15:26:30 GMT

Sorry for the delay. I am not sure how it works with Pivot sorting. However, will straight table works for you?

Re: Showing Error in Pivot Table

vishsaggi — Tue, 15 Aug 2017 15:28:06 GMT

stalwar1‌ Hey Sunny when you get time can you help here please.... Pivot sorting showing 0's on the top.

Re: Showing Error in Pivot Table

sunny_talwar — Tue, 15 Aug 2017 15:32:23 GMT

Checking...

Re: Showing Error in Pivot Table

vishsaggi — Tue, 15 Aug 2017 15:34:05 GMT

Appreciate it.