topic Re: Not showing Quantity Totals until in QlikView

Not showing Quantity Totals until

Fri, 17 Jun 2011 18:46:35 GMT

In a pivot table I do not want to show quantity amounts until the users drills down to part numbers (the table also shows sales).

So if the user is looking at the customer level the quantity amount is not relevant, so show the quantity as "blank" and show sales totalse. But if the user drills down to see the customer's part number detail, then show the appropriate amount in the quatity field in the pivot table.

How would I do this?

Thanks,

Stephen

Re: Not showing Quantity Totals until

johnw — Fri, 17 Jun 2011 23:54:22 GMT

Maybe this?

if(len("Part Number"),sum("Quantity"))

The idea is that it only does the calculation if there is a single part number associated with that row. You would still get a quantity if a customer, say, had only a single part number. Another approach that would remain blank even then would be to check the dimensionality() and/or secondarydimensionality(). The number to check for depends on how many dimensions you have. Replace the existing expression with just dimensionality() or secondarydimensionality() to see what the number is at the level you want. Then it would be something like this:

if(dimensionality()=4,sum("Quantity"))

Re: Not showing Quantity Totals until

Sat, 18 Jun 2011 12:24:52 GMT

Cant get it to work yet, but I was thinking along the lines of:

if (
count( aggr( NODISTINCT count( Quantity ), [Part Number])
) = 1, Sum( Quantity ), Sum(0)
)

This would however also give results if one dimension up and there is only one part underneath.

Perhaps you can fiddle around with that idea a bit,

gl Jeroen