8 Replies Latest reply: Sep 3, 2014 4:12 AM by Stefan van Diepen RSS

    Filtering Table Data

      Hello Qlikviewers!

       

      I have a set of data with Shipment (SHP)- figures per customer per month. Each customer gets a Category which differs every month. This Category depends on the delta between the shipments in the current month and the shipments in the same month last year. We have Categories W (Won), U (Up), D (Down) and L (Lost).

       

      Now I want to make a table, representing the top 5 of a Category (let’s say W) in a certain month. I currently have the following set-up:

       

      Dimension: Customer

      Expression: SHP 2014 = sum({$<Period={7},[RevenueYear]={2014},WULD_LYR={W}>} SHP)

       

      So far, so good. This expression gives me only the SHP information for the customers in Month “7”, Year “2014” in the Category “W”.

       

      Now I want to add a column with the delta between the month in this year (which I just calculated) and the same month in last year. I have written this:

       

      Expression: SHP Delta =[SHP 2014]- sum({$<Period={7},[RevenueYear]={2013}>} SHP)

       

      This is where things go wrong. Remember that the [SHP 2014] I’ve calculated returns a value when the Category is a “W”, but a 0 when the Category is otherwise. So if I deduct last year’s performance, for the ones with a Category “W” there is no problem. For the customer in other Categories however, we get all negative values.

       

      Does anyone have any idea how to solve this?

       

      Basically I need a way to let the table show only the customers which have a Category “W” in Month “7”, Year “2014”.

       

      Any help and ideas are appreciated!

       

      Kind regards,

       

      Stefan