Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
First post on community, not sure if posted in correct place as I wasn't given much option on where to post.
I have an issue where I have one chart which calculates some financial figures for each month, but the finance team want the same details which summarizes the data for all selected months/customers. I have worked this for most expressions, however, one of them 'Plan Monthly' is a derived field and doesn't exist as a field and is calculated - i don't seem to be able to accurately count a sum of the 'Plan Monthly' shown on screen.
The expression 'Plan Monthly' is as follows: MAX_FM_ACQ_BOOK_VAL_AMT/MONTH_BETWEEN
where MAX_FM_ACQ_BOOK_VAL_AMT is a figure of say £12000 and MONTH_BETWEEN is the programme length so say 12, therefore it shows £1000 a month in the chart.
Both of the these are variable depending on customer(s) selected. Is there anyway to SUM the 'Plan Monthly' based on the filters applied?
thanks,
Nick.
Dear Nick,
Its almost not possible to say anything on the issue unless you post the actual expression you have used in charts with list of dimensions.
Regards,
Kaushik Solanki
Hi Kaushik,
Thanks for your response.
The only dimension is Month_Year (Not an actual date due to data issues but in format YYYYMM)
The following expressions:
Billed Amount (-sum(BILLED_AMOUNT))
Actual Spend (Sum(TOTAL_VALUE_EXCL_VAT))
Plan Monthly (MAX_FM_ACQ_BOOK_VAL_AMT/MONTH_BETWEEN)
Spend Accrual ([Plan Monthly]-[Actual Spend])
Balance ([Billed Amount]+[Actual Spend]+[Spend Accrual])
As i posted before, i believe i have cretaed a chart which correctly summarizes everything apart from the 'Plan Monthly' expression as this seems to return nothing or the total amount for the entire programme and not the selected months.
Many thanks,
Nick.
Hi Nick,
Are this
MAX_FM_ACQ_BOOK_VAL_AMT/MONTH_BETWEEN
Field names or the expression alise name?
If they are field names coming from table then try
Sum(MAX_FM_ACQ_BOOK_VAL_AMT/MONTH_BETWEEN)
Regards,
Kaushik Solanki
They are field names.
I had tried the SUM function as my first attempt.
I think the issue is that Plan Monthly doesn't seem to be calculated until a customer is selected.