Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Does anyone have a solution on this?
Regards
Anna
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
Thanks for the tip!
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.
Any thoughts that might help?
Can you attach the qvw andI could take a look?
The file is attached, thanks!