same store with a twist - need help

[wizardo]

Hi,

I need to create a way of showing samestore sales comparison in a specific way.

I have tried many approaches and seen few examples in the forum.

unfortunately it seems my case has a twist not present in any of the places I looked.

hope some of you will be able to give me a push in a direction (any direction lol)

here goes:

there are two tables.

stores: holds for each "StoreCode" its "openDate" and "CloseDate"

transactions: holds the rows of sales for each "StoreCode" for each "SaleDate" with a link on "StoreCode"

a chart/str8table is needed

the users should be able to select 2 or more years.

they can also limit months and days of month.

the object should show the sales per each year accroding to the date range

so far its simple....

but it should only include Transaction for  specific store for a specific day if 

that store was opened at that same date in each of the currently selected years.

so if i select years 2007,2008,2009

and i select the date: may-22

i want to have transaction for a specific store only if it was Opened(active) on this date in all of the 3 selected years

so if a store was first open on may/23/2007 and was closed in 2010 then it should not be included

because it was opened on may/22/2009  and may/22/2008 but not on may/22/2007

also if the store was open on may/21/2007 but was closed on may/21/2009 it should not be included

because it was opened on may/22/2007  and may/22/2008 but not on may/22/2009

eventuely i would like to show it in a line chart with a line per year and month/week/day at the button exis

As I said have tried many things

but struck DeadEnd at each

hope some of you may help so i will not end up the same

daniel

[Anonymous]

Daniel,

The idea is that for each store (hence aggr by StoreCode below) use its sales if the store was open earlier than the earliest date in selection, and closed later than latest date in selection.  So, it maybe something in this direction:

sum(aggr(if(OpenDate<=min(Date) and closeDate>=max(Date), Sales),StoreCode))

Can be implemented in set analysis as well, but it's not essential.  I did not test, so just cosider it as a "push in any direction".

Regards,

Michael

[wizardo]

Michael,

Thanks for the push

I have tried using it "as is" initially. I only added a "sum" between the "aggr" and the "if", like this:

sum(aggr(sum(if(OpenDate<=min(Date) and closeDate>=max(Date), Sales)),StoreCode))

but it always returned null or zero values.

after trying various options to no avail,

I tried rethinking about the idea behind it.

at first glance the logic of it seems to make sense, but then i realized something

I am not sure I can exactly explain it but ill try with an example

lets say I have 2 store

store 1 was opened in January 2010 and closed on  December 2011

store 2 was opened in August 2010 and closed on  December 2011

now if I select months July, August, September

with your idea (however eventually implemented) the sales of store 1 would not be included

while store 2 will have its entire sales included.

what I need is that sales for store 1 will be included but only of the months that the store was opened both in 2010 and 2011

meaning August, September

store 2 will still have its entire sales included.

hope I made myself clearer this time

and also maybe you have an idea why the suggest expression didnt work

Thanks anyway,

Daniel

[swuehl]

Daniel,

how is the Date used in your expression linked to SaleDate of your transaction table? Are you using a master calendar?

And if you select months July, August, September, do you also select on year? If not, min(Date) may be dependent on the total year range you keep in your date (calendar?) table, so possibly much earlier than your 2010/2011 store dates of store 1 and store 2.

If possible, could you post a small sample?

Regards,

Stefan

edit:

Just reread your post:

wizardo wrote:

I have tried using it "as is" initially. I only added a "sum" between the "aggr" and the "if", like this:

sum(aggr(sum(if(OpenDate<=min(Date) and closeDate>=max(Date), Sales)),StoreCode))

but it always returned null or zero values.

That expression will not work, because you are embedding an aggregation function into another (min() function into sum() ), which is not allowed.

I think the original expression will not work, because the condition, if met, will return multiple Sales values per StoreCode, so the y-value of the aggr() needs to be unambiguous per dimension value (probably that's why you tried with the additional sum() ?).

What could work  is either

=sum(aggr( if(OpenDate<=min(total<StoreCode> SaleDate) and CloseDate>=max(total<StoreCode> SaleDate), Sale),StoreCode,SaleDate))

or

=sum({<OpenDate= {"<=$(=min(SaleDate))"},CloseDate={">=$(=max(SaleDate))"}>} Sale)

[Anonymous]

Stefan,

You're probably right about the original expression.  It is likely should be

sum(aggr(if(OpenDate<=min(Date) and closeDate>=max(Date), sum(Sales)),StoreCode))

Regards,
Michael

[swuehl]

Michael,

yes, that will work if the expression is used in a chart with dimension StoreCode or in a global context.

I've interpreted Daniel's post in a way that he intends to use Time / Date as dimension in his chart, so I believe the aggr() function needs to provide that dimension granularity (SaleDate in my example) as well to allow the containing chart limiting the results to the appropriate dimension values.

I think the set expression is somewhat more easy to maintain and understand in this scenario, besides the usual suspect 'more performant'.

Regards,

Stefan