Show data linked to a date dependent on selected date
Hi!
I have three dimensions that are linked together in the following way:
store ---many to one--> company ---many to one--> chain
All three dimensions have their own approval date, create date and some other dates.
I want to create a table showing number of stores, companies, and chains approved or created during the selected time period (see table).
Number of created/approved stores, companies, and chains during Januari and February 2011
Country
store
company
chain
Sweden
12
4
1
Norway
15
3
0
Finland
3
0
0
Denmark
5
0
0
I only want a company and a chain popping up in the table if the approval/create date of tha comapny or chain was during the selected time period and not all companies and chains connected to the new stores (as I get now). I.e. I get too many companies and chains for every time period (if I don't choose the entire period). This is because the dimensions are linked together.
My script looks something like this:
store:
store_id
company_id
chain_id
store_approval_date
store_create_date
...some more date and other attributes
company:
company_id
company_approval_date
company_create_date
...some more date and other attributes
chain:
chain_id
chain_approval_date
chain_create_date
...some more date and other attributes
I also want to do the same tables for create date and some other dates connected to the dimensions. I do not have a master calender (since I didn't get that to work without synthetic keys) so I use the store_approval_date as the date that can be selected in the application.
you could create a Data Island of a calendar file woth jsut dates in with no links and then do either Set analysis or Sum(If ) on each date indepndently then agains tthe Master Calendar
I have now created a calender island and in the expression I have following set analysis:
count(distinct{$<MonthYear=le_approval_YM>}leid)
So I want to count the number of distinct "leid" when the le_approval_date is the same as one selected MonthYear (from the master calender). But it doesn't work. It doesn't happen anything with my table when I select a MonthYear in the island calender.
