topic Re: Filtering Table Data in QlikView

Filtering Table Data

Anonymous — Tue, 02 Sep 2014 10:11:00 GMT

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

Re: Filtering Table Data

Gysbert_Wassenaar — Tue, 02 Sep 2014 10:14:47 GMT

sum({$<CustomerID=P({<Period={7},[RevenueYear]={2014},WULD_LYR={W}>}CustomerID), Period={7},[RevenueYear]={2013}>} SHP)

Re: Re: Filtering Table Data

Anonymous — Tue, 02 Sep 2014 10:47:33 GMT

Works like a charm! Thanks!

Can you explain to me how the formula you used works?

Re: Re: Filtering Table Data

Gysbert_Wassenaar — Tue, 02 Sep 2014 11:03:06 GMT

I've added a clause that limits the customers to those that are 'possible' when the filters Period={7},[RevenueYear]={2014} and WULD_LYR={W} are applied. That's what the P() function is used for.

Re: Re: Filtering Table Data

Anonymous — Tue, 02 Sep 2014 11:48:35 GMT

OK, that's clear, thanks.

One additional question: there are also customers which have no record for 7-2014, but that do have a record for 7-2013. They are in the Category L (Lost). Example:

2013-7 = 54

2014-7 = non-existent

Delta = -54

It should be showing the -54 as a delta, but the current formula doesn't show them, since they don't fit the P() because they are not possible in 7-2014. Any idea how to solve this one?

All the other Categories work fine!

Re: Re: Re: Filtering Table Data

Gysbert_Wassenaar — Tue, 02 Sep 2014 13:51:09 GMT

Maybe like this:

sum({$<CustomerID=P({<Period={7},[RevenueYear]={2014},WULD_LYR={W}>}CustomerID), Period={7},[RevenueYear]={2013}>+<Period={7},[RevenueYear]={2013},WULD_LYR={L}>} SHP)

If not, please post a document with sample data and explicit business logic that explains how the result should be calculated.

Re: Re: Re: Filtering Table Data

Anonymous — Wed, 03 Sep 2014 06:24:18 GMT

Nope, that doesn't work... I'll try to explain below with an example.

Customer	Period	Year	SHP	WULD
Philips	7	2013	15	W
Philips	7	2014	20	U
Shell	7	2013	15	W
Shell	7	2014	10	D
Apple	7	2014	50	W
Lenovo	7	2013	25	W

This is a very simplified version of the data, but I think it'll do for this purpose.

Since we started this data in 2013, all WULD's for 2013 are W (Won), since there is no comparison data in the source.

Now I want an overview per WULD-Category for 7-2014.

So Philips should be in U, Shell should be in D, Apple should be in W and Lenovo should be in L.

The first three are now going nicely with the first formula you gave me.

However the last Category (Lenovo in (L)ost) is not working.

This is - I think - because it doesn't fit the P(), since it doesn't exist in 7-2014.

Final output should be this:

The orange one is the one still missing a.t.m.

Thanks again for your help and effort!

Kind regards,

Stefan

Re: Filtering Table Data

Gysbert_Wassenaar — Wed, 03 Sep 2014 07:56:24 GMT

This will return the sum of SHP for the customers that did not exist in Period 7 in 2014, but did exist in Period 7 in 2013:

sum({$<Customer=E({<Period={7},[Year]={2014}>}Customer), Period={7},[Year]={2013}>} SHP)

Re: Filtering Table Data

Anonymous — Wed, 03 Sep 2014 08:12:26 GMT

Yes, yes, yes . Thanks so much for your help, looks amazing on my screen now!