topic Re: Create a Summary Chart from figures derived in another chart in Water Cooler

Create a Summary Chart from figures derived in another chart

Wed, 24 Jul 2013 08:31:51 GMT

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.

Re: Create a Summary Chart from figures derived in another chart

kaushiknsolanki — Wed, 24 Jul 2013 08:36:19 GMT

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

Re: Create a Summary Chart from figures derived in another chart

Wed, 24 Jul 2013 08:42:56 GMT

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.

Re: Create a Summary Chart from figures derived in another chart

kaushiknsolanki — Wed, 24 Jul 2013 08:55:30 GMT

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

Re: Create a Summary Chart from figures derived in another chart

Wed, 24 Jul 2013 09:06:37 GMT

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.