Pivot Table - Only Show Value When Filtered

[pkelly]

I have a pivot table with the following fields...

Cust Code | Qty | Sales | Gross Profit

Qty, Sales and Gross Profit being the total for each customer.

We sell various products in different units

e.g. Panels = number of sheets, Timber = cubic metres.

It is therefore illogical to sum the Qty field because of the different units.

What we would like to do is start off showing the Qty field as blank with the Sales and Gross Profit showing the summed values.

Add a product type filter (showing Panels / Timber) which, when selected, will allow the qty field to show a value (and filter on Sales / Gross Profit).

I suppose in summary, I would like the Qty field to be blank unless the product type filter has a value not equal to all...

[disqr_rm]

Would the "Calculation Condition" work for you?

In this field (under General Tab) you would specify count(distinct units) = 1. So this condition will fail, if there are more than on units. Based on this you can set an error message (button "Error Messages" under General tab) saying "Please select one Unit first".

This way, if you have more than one units available for your filters and user opens this chart, it will show the error message on the chart instead of the data.

If this doesn't work for you, try making an expression calculation like: if(count(distinct units) = 1, sum(Qty), 0).

If nothing works, just post here a sample qvw file and I am sure you will get it solved in minutes.

[Oleg_Troyansky]

If I understand your problem correctly, you would like to show quantity if a single U/M available (based on selection) or show blank when more than one U/M is available.

You can test it using function only() - it will return the value if only single value is available, or null() if multiple values are available. Your formula could like like this:

if( len(trim( only(UOM) )) > 0, sum(Quantity), null())

len(trim()) > 0 is a universal way to identify an existing value that is not empty and not null().

cheers,

Oleg

[pkelly]

Hi Rakesh

This is a good idea but unfortunately doesn't solve the problem for me - I was too simplistic in my original description of the problem.

Our products fall into product super groups.

For example, we have panel products whichhave a super group of panels with different units within this e.g. m2 and number of pieces.

We are only interested in the m2 quantities so force the items which are not stocked in this manner to be zero qty with a unit of n/a.

We could therefore have a scenario of...

Super Group | Product Type | Qty Unit

Panels | Plywood | m2

Panels | Edging Tape | number of units

This means that for panels we will have two unit types - m2 and n/a - so the qty won't be shown using count...

Hope that makes sense and thank you for your help...

[disqr_rm]

May be little bit of scripting can help.

If you do following (just an example) the count will return value 1 --> This won't work for you:

Map:
LOAD * INLINE [
Product Type, Unit
Plywood, m2
];

Qty:
LOAD * INLINE [
Super Group, Product Type, Qty
Panels, Plywood, 2,
Panels, Edging Tape, 4
];

But if you do following, every line has an unit assigned. For ones there is no unit, it will give a space, which will be caounted in the count.

Map:
MAPPING
LOAD * INLINE [
Product Type, Unit
Plywood, m2
];

Qty:
LOAD *,
applymap('Map', [Product Type], ' ') as Unit;
LOAD * INLINE [
Super Group, Product Type, Qty
Panels, Plywood, 2,
Panels, Edging Tape, 4
];

[pkelly]

Hi Oleg

Have been trying your proposed solution and I am nearly there with it...

In my extract, I pull in over two years worth of sales information and display this in various pivot tables - trends etc.

The pivot table I am working on just now compares the previous months sales with the same month last year.

I have the following formula...

July last year...

=IF(Dimensionality() > 0 AND (len (trim (only (pro_QtyDisplay) )) > 0), sum(if(cam_HistoricalMonthNumber=13,sin_Qty,0)))

July this year...

=IF(Dimensionality() > 0 AND (len (trim (only (pro_QtyDisplay) )) > 0), sum(if(cam_HistoricalMonthNumber=1,sin_Qty,0)))

NB - cam_HistoricalMonthNumber is a field I use to identify historical months - current month set to 0.

This almost gives me what I want.

The issue I have is the the only(pro_QtyDisplay) is working on the full data extract and not the two months which I want to display in the table.

So if customer A had only bought pro_QtyDisplay products = Volume in the two months I was looking at but say in June had bought a

pro_QtyDisplay product = Area, the quantity would not be displayed - as I am only looking at July I would want the quantity to be displayed.

I have tried various permutations building the cam_HistoricalMonthNumber criteria into the IF AND i.e. before the sum with no luck...

Any guidance would be greatly appreciated.

[johnw · Accepted solution]

First, I agree with Oleg's solution.

As far as making it work in your case, I believe you need to use the same condition in the only() as you use in the sum:

=IF(Dimensionality() > 0 AND (len (trim (only (if(cam_HistoricalMonthNumber=13,pro_QtyDisplay)))) > 0), sum(if(cam_HistoricalMonthNumber=13,sin_Qty,0)))

=IF(Dimensionality() > 0 AND (len (trim (only (if(cam_HistoricalMonthNumber=1,pro_QtyDisplay)))) > 0), sum(if(cam_HistoricalMonthNumber=1,sin_Qty,0)))

[pkelly]

Thanks John - worked a treat...

Regards

Paul