remove some results from report

m4u — Mon, 13 Feb 2012 08:56:20 GMT

Hi all,

I have a painful problem I've encountered many times.

Suppose i have sales data:

StoreName,Revenue,Month,Year.

I need to create a report (pivot chart) that will display the following columns:

store,current year sales (according to selected year),previous year sales,difference

Now, if a particular store do not have sales for current year, i want to remove him from the report (even if revenue exists for last year).

this is the structure of my pivot:

Dimensions:

store name

Expressions:

sum({<Year={$(=max(Year))}>} Revenue)

sum({<Year={$(=max(Year)-1)}>} Revenue)

i tried to change my dimension:

=if(Year=$(=max(Year)) and Revenue<>0 and not IsNull(Revenue),StoreName)

But it makes the column of the previous year empty.

I would appreciate very much any help.

Regards,

Shlomo

jagan — Mon, 13 Feb 2012 10:00:15 GMT

Hi,

Use the following expressions

Current Year = sum({<Year={$(=max(Year))}>} Revenue)

Previous Year = If([Current Year] = 0, 0, sum({<Year={$(=max(Year)-1)}>} Revenue))

If both the expressions returns 0 then the row is automatically removed by qlikview.

Regards,

jagan.

m4u — Mon, 13 Feb 2012 10:14:08 GMT

thanks a lot

topic remove some results from report in QlikView