Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

aaroncouran
Contributor III

Same Store - Set Analysis

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?

Tags (1)
1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

Same Store - Set Analysis

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 )

4 Replies
erich_shiino
Honored Contributor

Same Store - Set Analysis

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 )

aaroncouran
Contributor III

Same Store - Set Analysis

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.

Not applicable

Re: Same Store - Set Analysis

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.

Not applicable

Re: Same Store - Set Analysis

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?

Community Browser