topic Help with an expression... in QlikView

Help with an expression...

hopkinsc — Thu, 09 Jun 2011 14:41:49 GMT

Hi, i would like to combine the following 2 expressions but am unsure on how to do it..

Round(If([Deal Description] = '12721 - LUNCH TIME DEAL £3' or [Deal Description] = '200030 - LUNCH TIME DEAL £3' ,Sum(InDealQuantity)/3, Sum(InDealQuantity)/2))

and

{$<[Supplier] = >}

I basically want to add to the 1st expression the command to ignore any selection made from the 'Supplier' field.

Can anyone help please?

Help with an expression...

pat_agen — Thu, 09 Jun 2011 16:28:44 GMT

hi,

firstly you are better of doing a sum(if (...)) than an if(sum(..)) . Butthat depends on your data.

the sum(if()) will test each record for your condition and act accordingly, whereas the if(sum()) will test the condition once an then sum. given that you are testing [deal description] I'd say it is the sum(if()) you shoudl be using.

this makes adding your set analysis easier.

try this:

sum({$<[Supplier] = >}

if([Deal Description] = '12721 - LUNCH TIME DEAL £3' or [Deal Description] = '200030 - LUNCH TIME DEAL £3' ,InDealQuantity/3, InDealQuantity/3)

this should start by ignoring any supplier selections made elsewhere and then sum the IDealQuantity, dividing each line by 3 if part of the £3 deal and by 2 if not part of the deal.

does this make sense?

Help with an expression...

hopkinsc — Fri, 10 Jun 2011 07:52:56 GMT

Hi, thanks for your reply...

It didn't work for me using Sum(If, it gave me wrong figures, so i used the following which seems to work as i wanted...

Round(If ([Deal Description] = '12721 - LUNCH TIME DEAL £3' or [Deal Description] = '200030 - LUNCH TIME DEAL £3' ,Sum({$<[Supplier] = >}InDealQuantity)/3, Sum({$<[Supplier] = >}InDealQuantity)/2))

Thanks for your help