Sum Latest Entries

[#1]

Hi All

I have a table in my DB which stores Sales Forecasts.  For each Forecast (weekly) by Sales Rep, the will create a new Forecast Record and a value.

How would I use the Sales Rep as the dimension, but sum only their latest Forecast?

In the example below I need to return:

Bruce     20

Andy     4

Thanks

Date	Rep	Value
4 Nov	Bruce	10
4 Nov	Andy	5
11 Nov	Bruce	15
11 Nov	Andy	7
18 Nov	Bruce	20
18 Nov	Andy	4

Example of Records:

[agni_gold]

You can load data on the basis of load time . or you can load last inserted data in your chart. with condition in dimension tab .

[tresesco]

Use expression like:

=FirstSortedValue(Value, -Date)

when your dimension would be Rep.

[udit_kumar_sana]

Hi,

Please find the attachment for the solution.

Thanks & Regards,

Udit

[rustyfishbones]

You could try

But you need to ensure your Date Field is formatted correctly

[rustyfishbones]

Maybe Try

AGGR(FirstSortedValue(Value,-Date),Rep)

[#7]

Hi,

Try to use function Date(ReloadTime()) to get the previously loaded date and put this in your conditon with Date field.

i.e. Sum({<DateFld = {'>$(=Date(ReloadTime(),'DD MMM')'}>} Value)

[#8]

Hi Alan

Thanks

I am using a Straight Table Chart where the Rep Name is the Dimension, and need to return the Forecasted Value for each rep where the Value returned is the last forecast entered.

[#9]

Hi Udit

I have a straight table chart where:

Dimension is the Sales Team and expressions are

1. Budget

2. Forecast*

3. Variance (Column(2) – Column(1)

4. Prism

· Forecast this is the column I am trying to sum the VALUE where TYPE = ‘F’.

Problem:

Each Team can create multiple Forecasts and this is why I need to sum their latest forecast only.

This is my expression:

Sum({<Type={'F'},Date={'>=$(vFYPeriodStart)'}>} Value)