I have created a master calendar containing dates and the month end date (of the date) for the last five years. I have another table contaning the payments made. The payments made table contains the date the payment was made and also the month end date of the payment. I created a chart with the Calendar Month end date as the dimensaion and the following expression
sum( if (ClmPayMEDate = CalMonthEnd, ClmPaymentAmt)
I was expecting the sum of all payment amounts upto the month end for each month end date, but for some reason the amount is getting multiplied with the no of rows in the payment table contaning the same month end date. Both calendar and the payments table are not joined at the moment. Please can you let me know if I can achieve my results without joining the tables?
You have a cartesian product in your query because your two tables are not in relation.
Look at your tables (Ctrl-T). Normally, you may have a join between your tables (between your month End date in your calendar table and your Month End Date in your Payement table).
For this, both columns must have the same name (change it in the load if necessary).
Thanks for your reply. I actually want this to be a cartesian product. i.e. I want to display the month end dates from the calendar irrespective of whether there is a payment made in that month or not. if there is a payment made in that month then I want it be displayed alongside of that month.
you should still join your two tables but on the date field not the month end field. I would drop the PaymentMonthEndDate as this will just get in the way.
Your original expression was coming out wrong because although each payment has one and only one month end, each record in the calendar table has a month end. As your granularity in the calendar table is the date this will mean March having 31 Month ends all being the same. So If you join on month end or use your expression a payment falling in March will get multiplied by 31.
I take it you have created your calendar independently of the payment fact table. that is fine so you will have in there all the dates tou want and all the month ends. Now simply join the fact table to the calendar on the date key.
If you run a chart using the Calendar month end and an expression sum(PaymentAmt).; this will sum correctly and if you make sure zeros and nulls are visible (presentation tab -> supress missing is not ticked) then months with no payments will appear.
