How to build such calculated member?
-
Hi,
I have a cube with a fact table which has campaigns and customers to whom this campaign was marketed too. Each campaign has a lodgement date (the date when it was mailed to a customer). If a customer enquired about it, it would contain "1" in the line for this customer and campaign:
Campaign_CodeCustomerIdCount_of_MailingsCount_of_EnqLodgement_DateEnq_Date
Crtms_07 _____I__ 1 _____I_______ 1 _____I____ 0_____I___ 20080205 __I__ null
Crtms_07 _____I__ 2 _____I_______ 1 _____I____ 1_____I___ 20080205 __I__ 20080208
I have Compaign_Code, Lodgement_Date, Enquiry_Date dimensions and Count_of_mailings, count_of_enquires measures. I need a calculated member which would take all the enquires which happened in the first ten days after lodgement date for a particular campaign, multiply it by 2.5 and subtract the number of enquires that already occurred. If it has been over 30 days since the lodgement date for the campaign, it should show nothing or 0.
For example, I have a campaign which had been mailed to 1000 customers and in the first ten days I got 50 enquires. I view my cube in 15 days and by that time I have 60 enquires. The formula would look like this: 50*2.5 - 60 = 65.
In 20 days I have 85 enquires, so I should get: 50*2.5 - 85 = 40 etc.
I tried to do it on the fact table level, but it has individual records for each mailing for each campaign to each customer, so it is not suitable for storing the sums of enquires. I can’t put it into a separate table as I can only build measures from fact table columns.
I understand it should be done on the cube level using MDX, but I am not sure how to build this MDX expression.
Thanks.
-
The image did not work. I think we should be able to upload small images directly into the post.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply