topic help with expression in QlikView

help with expression

hopkinsc — Wed, 21 Jul 2010 12:09:05 GMT

Hi, i have an application which displays promotion statistics. i have a field called DealNumbers which has several hundres dealnumbers.

I then have an expression which calculates the total sales and divides it by 3.

I only want to have this expression to populate when a specific deal is selected (900000)

Any ideas?

help with expression

Wed, 21 Jul 2010 12:23:22 GMT

You could do something like:

=SUM(IF(GetFieldSelections(DealNumbers)='900000',Sales))/3

help with expression

hopkinsc — Wed, 21 Jul 2010 13:10:24 GMT

Hi,

Thanks for your reply, This doesn't seem to work for me though...

It works as far as selecting the correct deal number, i.e.

It displays a result when the deal number 900000 is selected, and displays - when the wrong deal number is selected, but it displays the wrong totals.

If i use

Sum (SalesQuantity) /3

then for the first result it displays '140'

If i also add in the expression

SUM(IF(GetFieldSelections([DealNumber])='900000',SalesQuantity))/3

so now both are side by side, it displays '1960'

Both are using the same fields and looking at the same data, but bringing back different information.

am i doing something wrong?

help with expression

Wed, 21 Jul 2010 13:24:56 GMT

Hi,

Plz see attachment.

Keep your table/expression as it is.

Create separat list box of DealNumbers.

Whenever you will click on any of the deal number, you will get expression for only that/those deal number/s.

Happy Thoughts

help with expression

Wed, 21 Jul 2010 13:25:25 GMT

You could use the expression

sum ( if (DealNumbers = 900000 , Sales , )) / 3

Or would be easier if you could upload some sample data to explain your problem

help with expression

hopkinsc — Wed, 21 Jul 2010 13:33:14 GMT

Thanks but that won't work for me as not all deal numbers are to be divided by 3. some are divided by 5, some by 2 etc.

help with expression

Wed, 21 Jul 2010 13:40:11 GMT

Plz share sample data with expression in QV.

help with expression

hopkinsc — Wed, 21 Jul 2010 15:04:38 GMT

Hi, here is my document,

Sorry its so big, Having trouble trying to replicate it.

The DEALS SALES TOTAL is the expression given on this forum (which is displaying the wrong info.

The =sum(salesQuantity)/3 is displaying the correct info, but for all deal numbers. where i only want a specific deal number to display info, the rest be a zero or '-'.

For the attached document, i am specifying deal number 4986, not 900000 as posted in my original post.

Help would be appreciated!

help with expression

hopkinsc — Wed, 21 Jul 2010 16:33:46 GMT

Any ideas anyone?

help with expression

nathanfurby — Wed, 21 Jul 2010 17:17:40 GMT

If it was me then I would create a mapping table with the necessary factors at load time and then perform calc on that. (See attached example)

You could also just create a calculated column (Deal Sales Total) on load time using a mapping table and then reference the column directly.

Hope it helps

help with expression

hopkinsc — Thu, 22 Jul 2010 09:24:15 GMT

Thanks everyone for your replies, i managed to get it working by using:

If (DealNumber =900000 , sum (salesQuantity)) / 3

🙂

help with expression

nathanfurby — Thu, 22 Jul 2010 09:29:57 GMT

How does that expression work when the DealNumber does not equal 900000?

Also - what happens if other deal numbers need a factor of some kind, or what if the factor changes? Much better to manage this with data rather than in the expressions - imho

help with expression

hopkinsc — Thu, 22 Jul 2010 09:46:17 GMT

If it does not equal 900000, it just displays -.

We are only using this application to report on a specific deal number (900000) the application can be used to look at other deals too, but no calculation will be needed.

Currently our API database does not dump out the table required to give us the result needed (but it will later on this year), so currently we would not require other deal numbers to have specific factors.

🙂