topic Re: Sum IF with expressions in QlikView

Sum IF with expressions

tomdabek — Mon, 28 Mar 2016 19:40:33 GMT

I have a chart (straight table) that displays:

Source (Dimension)

Product (Dimension)

ListPrice (Expression - this is a value from a table)

Volume (Expression)

ASP (Expression - this is a calculation [Revenue]/[Volume])

There can be many rows of data returned, but there is a ListPrice for all combinations of Sources and Products.

I want to add a columns that are expressions that will show the volume for all records where the ASP is less than the ListPrice (or above ListPrice).

I've tried these but they don't work, the sum of the two columns is less than the total volume shown.

sum(if(Revenue/Volume<ListPrice,Volume))

sum(if(Revenue/Volume>=ListPrice,Volume))

This seems like a simple problem, but I cannot figure out how to create an expression that will show the value I am trying to display.

kkkumar82 — Tue, 29 Mar 2016 03:09:12 GMT

Hi,

Can you have try like this

sum({<ListPrice={">(Revenue/Volume"}>}Volume))

Thanks

Kiran Kumar

kkkumar82 — Tue, 29 Mar 2016 03:35:25 GMT

Hi Tom,

I am not sure why my expression is not working but I tried with your kind of expressions.

Created a field Revenue/volume as avg.

Please find attached.

Hope it may help you.

Kiran Kumar