Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

siepe1990
New Contributor II

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

1 Solution

Accepted Solutions

Re: Filtering Table Data

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


talk is cheap, supply exceeds demand
8 Replies

Re: Filtering Table Data

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


talk is cheap, supply exceeds demand
siepe1990
New Contributor II

Re: Re: Filtering Table Data

Works like a charm! Thanks!


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

Re: Re: Filtering Table Data

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.


talk is cheap, supply exceeds demand
siepe1990
New Contributor II

Re: Re: Filtering Table Data

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

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.



talk is cheap, supply exceeds demand
siepe1990
New Contributor II

Re: Re: Re: Filtering Table Data

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

CustomerPeriodYearSHPWULD
Philips7201315W
Philips7201420U
Shell7201315W
Shell7201410D
Apple7201450W
Lenovo7201325W



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:

Temp.png

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

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)


talk is cheap, supply exceeds demand
siepe1990
New Contributor II

Re: Filtering Table Data

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

Community Browser