Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
camilo
Contributor III
Contributor III

Same Store Sale Indicator

Hi, I´m trying to create a SSS indicator (on a pivot table) to compare sales 364 days after the selected date, something like:

sum sales if 364 day ago Sales<>0

How can I create this value? please any help is more than welcome.

Thanks,

Camilo

1 Solution

Accepted Solutions
swuehl
MVP
MVP

That's too bad

Well, if the 52 weeks are a fixed parameter, I guess we could really use a flag in the script.

It would be a bit more complicated than a simple StoreOpenDate, we have to calculate the Sales sums per Store and Date, check for any given Date the sums 52 weeks back and there we go, maybe like this:

SSSSales:

LOAD Date as Date,

Local,

Local &'-'& num(Date) as LocalDate,

sum(Sales) as SumSales

resident Input group by Local, Date;

SSSDates:

left join (Input) Load Date, Local,

if (Lookup('SumSales','LocalDate',Local&'-'&num(Date-364),'SSSSales')>0, 1) as SSS

Resident SSSSales;

drop table SSSSales;

Please have a look at attached sample, I added a second store too ensure we got the SSS correct per Store (look at the demo date in load script, only 31.8./1.9.2010 differing).

Hope this helps,

Stefan

View solution in original post

21 Replies
swuehl
MVP
MVP

Camilo,

I think your problem is similar to the problem I just worked on here:

http://community.qlik.com/thread/34402?tstart=0

You probably want to limit your sums to shorter period of times, you mentioned day period, so the set analysis looks maybe like

{<Date = {"=$(=max(Date))"} >}

resp

{<Date = {"=$(=max(Date)-364)"}>}

And you have to adapt the check for Qty > 0 according to your sum of Sales requirement, but that should be easy.

Hope this helps,

Stefan

camilo
Contributor III
Contributor III
Author

Hi Stefan,

I´m trying to use your aproach but probably I´m not using it properly, this is the code:

=if(sum({<Fecha={"$(=(Fecha)-364)"}>}Venta6.0) >0, sum(Venta6.0))

What I´m trying to do is to go 364 day before each selected date and see if sales were bigger than zero if that the case sum the selected dates sales (not including sales of the selected dates wich didn´t had any sales 364 days before).

Please if I´m not being clear enough tell me.

Thanks!

Camilo

Venta6.0 = Sales

Fecha= Date

swuehl
MVP
MVP

Fecha is a field, so $(=(Fecha)-364) is not giving a unique answer (unless you selected exact one value from field). Also I think there should be an equal sign before the dollar sign.

try

=if(sum({<Fecha={"=$(=max(Fecha)-364)"}>}Venta6.0) >0, sum(Venta6.0))

to limit the date. And you may also need to clear any other date field in the set expression, like Year, Month:

=if(sum({<Fecha={"=$(=max(Fecha)-364)"}, Mes= >}Venta6.0) >0, sum(Venta6.0))

Maybe it's possible to use the set identifier 1 instead of $, this will disregard your current selections:

=if(sum({1<Fecha={"=$(=max(Fecha)-364)"}>}Venta6.0) >0, sum(Venta6.0))

Regards,

Stefan

camilo
Contributor III
Contributor III
Author

Hi Stefan,

I´m trying your approach but the total sales are bigger than it should, I think that using max(Date) doesn´t allow a full comparision between date sales?

I´m attaching an example hoping to clarify what I´m saying.

Thanks,

Camilo

chriscammers
Partner - Specialist
Partner - Specialist

This is very interesting but "Same Store Sales" is a very fundamental measure in the retail space if it were me I would not mess around trying to calculate it in a chart. If you put a Same store sales flag in your fact table then you could multiply the sales amounts by the flag and get the appropriate total.

So I assume on your fact table you have store and data of sale so if you add the store open date you can then  evaluate the difference between the Date of sale and the Open Date and set the flag based on whatever threshold is appropriate.

swuehl
MVP
MVP

Hi Chris and Camilo,

if I wouldn't trust that the calculations in the chart are as accurate as in the script in principle, I think I could stop working with QlikView. Currently we are just throwing in some ideas how to get the things done, I believe nobody wants to get into production without a thorough V&V.

I also prefer a more simple solution over a more complex one, so if SSS is all about the store opening date, Chris' suggestion using just the opening (or first selling date) is probably a good idea. I was assuming that we need to take the Sales comparision with 52 weeks back as given requirement.

Camilo, if you still need the comparison with the sales amount 52 weeks back for any given date, I worked out a solution, please find attached.

I corrected the expression (Look at the expression in the text box, I changed the search expression to a literal, not sure yet why the first has not worked.

So, if you select a single date in the bottom left, you should get the correct result in the text box next to it (for a single selected date).

But I assume you need a date range (like in a chart or table). I think that is problematic with the current solution, because the date as dimension conflicts with the date used in the set expression.

To work around this, I created a separate date field in the script, just as timeline for the tabe/graph. Please look at the second tab in the attached application.

Having the separate time line, you can use simple if-statements to check for past sales and filter the data accordingly.

Regards,

Stefan

camilo
Contributor III
Contributor III
Author

Yes Chris I already did what you say using the open date and using something like (Open Date <= Date-364) but at least here SSS is based on a day to day basis, If I just use the open date I will be taking into account those days that didn´t had any sales last year.

camilo
Contributor III
Contributor III
Author

Hi Stefan,

In deed I need date ranges, I think you are very close on your aproach using an auxiliary date, but I´m using pivot tables and when I transform your Straight Table to a Pivot one a got discrepancies on the agregated data.

I´m attaching your example with the pivot table I´m talking about.

Thanks for your help!

Camilo

swuehl
MVP
MVP

Yeah, we need to rebuild the sum of rows in a pivot table using advanced aggregation:

=sum(aggr(if(Sum(if(Date=SSSDate-364,Sales)>0),sum(if(Date=SSSDate,Sales))),SSSDate))

(use this as expression in the pivot table)