topic Same Store - Set Analysis in QlikView

Same Store - Set Analysis

Anonymous — Tue, 19 Apr 2011 21:00:13 GMT

I have a situation where I am wanting to compare sales numbers from two different periods, but I only want to include sales for stores that existed in the prior period. The duration of the period and how far back it is are flexible so I must do this in the front-end.

Example:

Year Store Sales

2010 123 10,000

2011 123 10,000

2011 456 15,000

Store Sales 2010: 10,000

Same Store Sales 2011: 10,000

Any thoughts?

Same Store - Set Analysis

erichshiino — Tue, 19 Apr 2011 21:06:25 GMT

You need set analysis with advanced searches.

From the help, you need something similar to the second example:

Set Modifiers with advanced searches

Advanced searches using wildcards and aggregations can be used to define sets.

Examples:

sum( {$-1<Product = {"*Internal*", "*Domestic*"}>} Sales )
returns the sales for current selection, excluding transactions pertaining to products with the string 'Internal' or 'Domestic' in the product name.

sum( {$<Customer = {"=Sum({1<Year = {2007}>} Sales ) > 1000000"}>} Sales )
returns the sales for current selection, but with a new selection in the "Customer" field: only customers who during 2007 had a total sales of more than 1000000.

You can use something like:

sum( {$<Store= {"=Sum({1<Year = {2010}>} Sales ) > 1000000"}>} Sales )

Same Store - Set Analysis

Anonymous — Tue, 19 Apr 2011 21:24:09 GMT

Thanks Erich,

That was the boost I needed. I am ending up with something like:

SUM({$<Store={"=SUM({1<Period={2}>}Sales)>0"}>}Sales)

So it will only include the stores that had sales greater than 0 in the previous period.

Thanks again for quick response. Awesome.

Re: Same Store - Set Analysis

Mon, 11 Mar 2013 18:44:09 GMT

Hello,

I've been able to use this post to get very close to the solution I'm looking for - but I'm not quite there yet. I have a chart that has dimensions of Year and expressions of [Comparison Date], [All Store Sales], and [Same Store Sales] that I am attempting to get this to work in. I have a calendar object, that when a date is selected, the previous few years are displayed along with their corresponding [All Store Sales] and [Comparison Date] which is a variable found with the following:

only({$<Weekday={$(=$(vSelectedDate_Weekday))}, WeekNumber={$(=$(vSelectedDate_Week))}, Date=, Store=, Year = {"<=$(vSelectedDate_Year)"}>} Date)

The variables within that variable are simply retrieving the weekday, week, and year of the selected date in the calendar object. The comparison date is the date we're interested in for same store sales since it's the same day of week in the same week for the year prior. I've tried several different instances to get the expression [Same Store Sales] to work but haven't had any luck. However, I can get it to work if I hardcode a date into the expression like the following:

sum({$<Store={"=SUM({1<Date={'3/11/2012'}>}DailyTot_Sales)>0"}>}DailyTot_Sales)

That expression will return a value for the top row as shown below:

Same Store Comparison for Sunday - 3/10/2013

Year Comparison Date All Stores Sales Same Store Sales

2013 3/10/2013 100,000.00 95,000.00

2012 3/11/2012 85,123.00 0.00

2011 3/13/2011 80,200.00 0.00

2010 3/14/2010 63,600.00 0.00

I'd like to be able to somehow reference the [Comparison Date] column (which is = $(vComparsonDate)) when calculating my [Same Store Sales]. I've tried that in many ways including the following but haven't had any success:

sum({$<Store={"=sum({1<Date={$(=$(vComparisonDate))}>}DailyTot_Sales)>0"}>}DailyTot_Sales)

I've tried several different syntaxes as I've found several set analysis examples referencing variables but haven't had any luck. I've also tried to convert the Date column and vComparisonDate to number columns and had the same results.

Can anyone offer any suggestions? Thanks in advance.

Re: Same Store - Set Analysis

Tue, 19 Mar 2013 14:42:45 GMT

Perhaps I should try and simplify my question from above. Above, Erich and Aaron cite a couple examples of using Set Modifiers with Advanced searches. I've been able to get this to work as well, but only if I hardcode a value in my set analysis. Is there anyway to use a Set Analysis with Advanced searches with a variable such as the following:

sum({$<Store={"=sum({1<Date={$(=$(vComparisonDate))}>}Sales)>0"}>}Sales)

In this case the variable vComparisonDate is a single value in the format '3/15/2013'. I've also tried this using a num as well without any luck. Am I trying to accomplish something that isn't possible with this syntax?