Help with Same Store Sales

[#1]

Hi all,

I've been struggling to get an advanced set analysis to work to retrieve same store sales and I feel like I'm very close, but it doesn't work the way I'd like it to yet.  My QlikView document has a very simple data model - a Sales table with the columns of storenumber, date and sales.  The data contains about 50 stores worth of data, all which have opened at various time over the past decade.  In my document I have a calendar object and when a date is selected, I'd like to be able to compare the same store sales for a selected date for the comparable date from the year prior.  Currently, I'm able to get the following set analysis to work when I hardcode a date within:

sum({$<Store={"=sum({1<Date={'3/21/2012'}>}Sales)>0"}>}Sales)

So this is capturing all stores' sales who had sales greater than 0 for that given date.  Ideally, I'd like to be able to reference a variable in that set analysis - something like this:

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

Unfortunately, I can't get this to return a value.  The variable works and sets the date correctly.  I've also tried to convert this to a num along with the date column and that also didn't work.  I've tried many different syntaxes similar to above but haven't had any luck.  Am I trying to do somthing that isn't possible in QlikView?

Thanks!

[#2]

Does vDateLastYear contain a value or an expression? If it's a value, then

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

should work. If it's an expression then

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

should work. Note that vDateLastYear expression will be evaluated once for the whole chart and not for each dimension.

[kaushiknsolanki]

Hi,

     Try this

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

     I just removed one extra } after 1 in your expression.

Regards,

Kaushik Solanki

[#4]

Thanks for the quick response.  The variable vDateLastYear holds the expression "max(CorrespondingDatePreviousYear)" and returns the date '3/21/2012' for example when I select a date of 3/20/13.

I tried both syntaxes and they both return 0.00000 unfortunately.  Could the problem be related to the variable?

[#5]

Thanks for the response.  This is actually returning a value, but it's the value of all store sales for the selected date - not of the date of the variable.

[#6]

Thanks for the response - this is returning a 0 for me unfortunately so doesn't seem to work.

[#7]

Kaushik is on the right track. The expression above will give you the sales of the stores with sales as of vDateLastYear. It seem you want the sales as of vDateLastYear for stores that had sales as of vDateLastYear. So you need an additional field modifier:

     sum({$<Store={'=sum({1<Date={"=$(vDateLastYear)"}>}Sales)>0'}, Date={"=$(vDateLastYear)"}>}Sales)

If you have any additional date selections, you will need to add the fields to the to list of field modifiers.

[#8]

Thanks, but I'm actually getting the same result with this syntax as I was with Kaushik's.  It gives a value, but it is all stores as of the selected date - it's not excluding stores not present from the year prior.  I was able to piece something together that works though with all of the suggestions:

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

Putting the '=' inside the parenthesis on vDateLastYear seemed to do the trick and I think I'll be able to work with this for what I need.  Thanks all for the responses.