Like for Like

[#1]

Is there an easy way to exclude values from an expression that don't appear in all returned values of the dimension. ie i would like to be able to only return Like for Like values.

eg I want to be able to compare the footfall of our shopping centers on a like for like basis.

I have already read the discussion http://community.qlik.com/thread/1342 but this did not work for my circumstances as want to be able to change selections and circle dimensions.

Many thanks,

Lawrence

[stephencredmond]

Hi,

You appear to have adequately explained what you don't want, but not exactly what you do want.  Perhaps you could post an example QVW showing what you are looking for?

I am guessing that a P() set might do the trick, but I can't give you a good response without knowing what you need.

Regards,

Stephen

[#3]

Stephen,

Thanks for responding, I have attached the QVW file I am working on. I deally I am looking to have a flag or button where I can switch from all results to just like for like results.

in the example below I would like to exclude sites that don't appear in all four years, as this screws the results and therefore need a like for like comparison.

Many thanks,

Lawrence

[stephencredmond]

Hi Lawrence,

Bit of a challenge there - thanks!  In fact, I have just blogged on this subject: http://qliktips.blogspot.com/2011/06/and-mode-in-set-analysis.html

You want to see only sites that have footfall in each year.  The default search (and Set) in QlikView is "OR" whereas you want an "AND".

So, in your script, you will need to load a separate Footfall Year field:

FF_Year:

Load Distinct

          SiteName,

          Year(FootfallDate) As FootfallYear

Resident Footfall;

Add the new field to the layout (you can remove it later if you want but it might be handy) and set the "AND-mode" option in the properties.  You might need to reload at this stage.

Then you can use this expression in your chart:

sum({<FootfallYear=P({$} Year)>} [In Count])

Hopefully this gets you along further.

Regards,

Stephen

[#5]

Hi Stephen,

Thanks for this and you blog posting, that commuicated my problem it more clarity than I could have managed. Your solution work a treatt, however is there an easy way to switch the selection between AND and OR.

Lawrence

[stephencredmond]

Hi Lawrence,

The easiest way is to have 2 fields - one for AND and one for OR.

You can then change your expressions and show/hide fields based on whatever way you want to switch them (button, etc.)

Stephen

[#7]

Hi Lawrence & Stephen,

I'm still on PE, so can't open you qvw, but think there may be an easier solution like:

Sum( {$<SiteName = { "=(count( DISTINCT Year(FootballDate) )=4)" }>} [In Count])

Tested it on the example from Stephen's blog

Sales:
LOAD * INLINE [
Customer, Year, Sales
Customer A, 2009, 111
Customer B, 2009, 111
Customer C, 2009, 111
Customer A, 2010, 222
Customer B, 2010, 222
Customer C, 2010, 222
Customer A, 2011, 333
Customer C, 2011, 333
];

Sum( {$<Customer = { "=(count( Year )=3)" }>} Sales)

That worked, so thinking might work for you too.

hth Jeroen

[#8 · Accepted solution]

The solution is extremely simple. I use the function aggr that returns a set of rows for each combination of the fields.

The case is the following:

A table with 4 fields: Year, Month, Shop, Sales, having data for 2010 and 2011.

The Like for Like KPI for a shop should compare only the months having sales on both years.

The job is done by a variable that returns only the records having sales on the same month:

//Variable L4L_Sales(year1, year2): Returns the sales of Year1 if there are sales on the same month of year2.

SET L4L_Sales = aggr(if((sum({$<[Year]={$1}>}Sales) > 0) AND (sum({$<[Year]={$2}>}Sales) > 0), sum({$<[Year]={$1}>}Sales)), Shop, Month);

In a grid chart then we can use the following expression:

L4L Percent: (sum($L4L_Sales(2011, 2010)) - sum($L4L_Sales(2010, 2011)))/sum($L4L_Sales(2010, 2011))

Check the attached qvw.

[makkemik]

Hello Angelos,

I like your approach it is extremely interesting.

Do you have any example for me about L4L analyse;

I have four Table;

1- Clients Table

2- Store Table (including StoreCode, StoreName, GPS locations, Store opening Date and Closing date, Etc...)

3- Calender Table

4- Sales Transaction Table (including StoreCode, ClientCode, Date, Qty, SalesSum, InvoiceNo, InvoiceLineID, Etc.)

I need a pivot chart total sales and L4L basis according to Store Opening dates. we will take MonthStart(OpeningDate)

Could you please help and share any example. My data is nearly 2 M row. Thanks

Kind regards

Murat