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

    Filtering Table Data

    Stefan van Diepen

      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,