topic Help with Set Analysis in QlikView

Help with Set Analysis

hopkinsc — Fri, 12 Nov 2010 16:13:54 GMT

Hi,

I need to write an expression to show: sum(SalesQuantity) but to ignore a dimension (PriceOverride). I have not not a clue how to write this, can anyone help please?

Thanks

Chris

Help with Set Analysis

Fri, 12 Nov 2010 16:18:57 GMT

if you mean a selection in the field PriceOverride, then:

sum({<PriceOverride=>} SalesQuantity)

Help with Set Analysis

hopkinsc — Fri, 12 Nov 2010 16:24:35 GMT

Hi nick,

No, i mean to ignore the dimension completely. Basically i have a calculated dimension which is hidden, so the user cannot select it anyway. The dimension is written to only display PriceOverrides which = yes, then i have ticked the 'Supress When Value is Null' option. I need to show sales no matter what the PriceOverride is equal to, so that is why i need an expression to completely ignore that dimension.

Thanks

Help with Set Analysis

Fri, 12 Nov 2010 16:41:28 GMT

You cannot ignore a dimension in a chart. There is a possibility to sum(total <fields>). Or you can Show Partial Sums for the dimension PriceOverride if you use Pivot Chart.

Help with Set Analysis

hopkinsc — Fri, 12 Nov 2010 16:53:11 GMT

Hi Nick,

No that won't work for me as it shows the sum of salesquantity or ALL customers instead of seperating them.

In QLIKVIEW help - Set analysis, there is an expression which returns total sales within the application, disregarding both selection and dimension. sum( {1} Total Sales )

So if ALL dimesions can be ignored, surely there is a way to only ignore one dimension??

Help with Set Analysis

pover — Fri, 12 Nov 2010 17:04:07 GMT

Chris,

I think it would be helpful to upload a small sample of what you're working on in a QlikView file with a excel table of the result you want because you've been battling with this for a couple days and nobody seems to completely understand the problem to help you.

Regards.

Help with Set Analysis

hopkinsc — Fri, 12 Nov 2010 17:19:45 GMT

Here is a sample...

Thanks

Help with Set Analysis

johnw — Fri, 12 Nov 2010 19:36:51 GMT

Replace your calculated price override dimension with field PriceOverride. Change expressions to these:

if(PriceOverride='yes',sum({<LineType-={'V'}>} [Reduced Quantity]))
if(PriceOverride='yes',(CurrentRSP-ItemPrice)*Sum(SalesQuantity))
if(PriceOverride='yes',sum({<LineType-={'V'}>} total <Customer> SalesQuantity ))

Help with Set Analysis

hopkinsc — Sat, 13 Nov 2010 12:07:54 GMT

John, thanks for your reply. How would i then only display PriceOvverrides =yes? I don't want to display any of the PriceOverrides=no.

This part of the app i am writing is for Reduced to Clear sales, so when the user maximises the Reduced to Clear chart i only want it to display Reduced to Clear sales without having the user to make selections.

But i still need ALL sales to be loaded in, as other charts in this app will relate to different sales.

Thanks

Help with Set Analysis

johnw — Mon, 15 Nov 2010 18:41:45 GMT

Since none of the expressions above will return a value for a PriceOverrride='no', then the default suppression of nulls should remove those rows.

Help with Set Analysis

hopkinsc — Tue, 16 Nov 2010 14:08:07 GMT

Thanks John,

I don't think i explained what i wanted very well...
I have attached a sample...
If you look at the sample, you can see that it is displaying PriceOverrides for both 'yes' and 'no'. I only want it to display the sales relating to 'yes'.
With it how it is, the 'Total Sales' figure is correct. i.e.

3
6
9
2

I can't see a way of hiding the PriceOverrides = 'no' unless i add in a calculated dimension
IF((PriceOverride = 'yes' ), PriceOverride)
then ticking 'Suppress null vales', but if i do that, the 'Total Sales' change and only total the Reduced Sales.
What i want is to only display sales relating to PriceOverrides = 'yes' but for the 'Total Sales' to be unaffected and actually show the full sales as it does when both PriceOverrides = 'yes' and 'no'
Do you know how i can achieve this please?
Thanks for your help
Chris

Help with Set Analysis

johnw — Tue, 16 Nov 2010 19:35:16 GMT


hopkinsc wrote:Do you know how i can achieve this please?

Had you applied my fix to the sample file you posted, and not to some new file, you would have seen that it worked. Your original example had three expressions. The expressions I gave nulled out the values if the price override isn't 'yes'. Therefore those rows were suppressed.

But instead you made a new example with FIVE expressions. So now you need to do the same thing for the expressions you've added in your new example. In other words, follow the pattern indicated by the three expressions I gave you:

if(PriceOverride='yes', YourCurrentExpression)

Also, you have an expression that is very specifically looking for a price override of 'no'. That needs to be removed, as it is meaningless if you're intending to remove those rows.

See attached.

Help with Set Analysis

hopkinsc — Thu, 18 Nov 2010 10:57:23 GMT

Hi John,

Thanks for your help, sorry about the expressions, i meant to disable them before posting the example.

I have copied what you have posted and it works great for the current selections. But i have noticed that the total sales are wrong if i clear the selections and look at more than one product at a time. As soon as i select ONE product again, the sales are correct.

I have attached another sample, I can't figure out ehy it is correct when looking at individual products but incorrect when looking at multiple products.

Thanks for your help again.

Chris

Help with Set Analysis

johnw — Thu, 18 Nov 2010 18:28:17 GMT

The underlined bit...

if(PriceOverride='yes',sum({<LineType-={'V'}>} total <Customer> SalesQuantity))

...says we want to ignore all dimensions but Customer. So one of the dimensions we're ignoring is the product, so that's why you're seeing what you're seeing. I only listed Customer because you specifically said you wanted to separate the customers, and because that was sufficient to give the correct results in your original sample file. It sounds like you want to add at least add Product to that list. There may be other dimensions from your chart that you want to include as well. Perhaps even ALL of them EXCEPT for PriceOverride:

if(PriceOverride='yes',sum({<LineType-={'V'}>} total <Customer, [Product Code], [Prod Desc], EAN, Size, Narrative, CurrentRSP, ItemPrice> SalesQuantity))

You've also now removed PriceOverride as a dimension, which will probably cause serious problems with the if(). If any of these rows have both price override 'yes' and price override 'no', then they'll be excluded. You won't see just the 'yes' data. If you don't want to see the price override column, hide it on the presentation tab instead of removing it, because how I wrote the expressions depends on it. There's probably also a solution that doesn't require it, but I don't feel like starting over.

I recommend reading help or the reference manual or whatever to try to figure out what the code I posted is doing and how it's doing it. That should help you adapt it to your real situation as you add other dimensions and expressions and rows of data that weren't in the original example.