Discussion Board for collaboration related to Creating Analytics for QlikView.
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
Solved! Go to Solution.
sum({$<CustomerID=P({<Period={7},[RevenueYear]={2014},WULD_LYR={W}>}CustomerID), Period={7},[RevenueYear]={2013}>} SHP)
sum({$<CustomerID=P({<Period={7},[RevenueYear]={2014},WULD_LYR={W}>}CustomerID), Period={7},[RevenueYear]={2013}>} SHP)
Works like a charm! Thanks!
Can you explain to me how the formula you used works?
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.
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!
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.
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
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)
Yes, yes, yes . Thanks so much for your help, looks amazing on my screen now!