Replicate Daily Data across the month

[maleksafa]

Hi,

I have monthly budget data which is stored at the first day of the month and represents the budget of the whole month, what i am trying to do is to take this amount, divide it by the total number of days of the month and spread it across the days of the month.

i don't want to do this in the load script as i will end up with a huge number of records so i am trying to accomplish this from the design side.

i have attached a sample document where you will see a chart displaying two monthly budget values (Jan and Apr), i need to take the budget of Jan (25000) divide it by 31 -> 806 and display this value on all the days of Jan.

thx

[jyothish8807]

Hi Malek,

Try to create a master calander so that you may have all the dates within the month, then use the expression:

Sum(Budget)/round(MonthEnd(TransactionDate)-MonthStart(TransactionDate))

Regards

KC

[maleksafa]

Hi,

i already have a master calendar in my schema, your expression work fine to get the daily budget, however i am not able to use it in my chart as i have transaction date as a dimension and the monthly budget is only available at the beginning of the month, in other words if i add the expression it will only show on the first day of the month.

[jpenuliar]

Hi Malek,

Since your Transaction date always start at first of Month, and represents the whole Months Budget, I will suggest convert that field into MM-YYYY and link it to Calendar Month/Year Field  not to Calendar Date Field, This way, all the dates in January will show with Monthly Budget figure. then you can proceed to compute for number of Days in Month to divide with Month's budget.

[maleksafa]

this is an option, however in the table where i have the budget data i have other type of transactions that are linked to the calendar table on the transaction date field so adding another key will create a synthetic key.

[jpenuliar]

can you post your Table view?

[maleksafa]

it is available in my original attachment.  but this is only related to budget data, other data are also included in the transactions table

[jpenuliar]

not of your current attachment, but of the whole schema your working on,

In terms of the  synth key that will result from my suggestion,

Can you not have a separate table for Month and Budget to Map into your Transaction Dates instead?

[maleksafa]

unfortunately i can't have a separate table as i have some fields in common with the main transaction table and doing that will result in a circular reference loop, i need to have the budget data in the transactions table.

[maleksafa]

thx for the help, i was able to solve it now using the below expression

aggr(NODISTINCT sum({<H_Flag={'MonthlyBudget'}>} d_chk_ttl),[AZ Year],WK_Month)

/

aggr(NODISTINCT count([Transaction Date]), [AZ Year],WK_Month)

having only transaction date as a dimension, i have aggregated the budget by Year and Month and using the NODISTINCT function it will distribute the same value across the month.