topic Sum Latest Entries in QlikView

Sum Latest Entries

Mon, 02 Dec 2013 11:32:28 GMT

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:

Re: Sum Latest Entries

agni_gold — Mon, 02 Dec 2013 11:39:07 GMT

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 .

Re: Sum Latest Entries

tresB — Mon, 02 Dec 2013 11:50:33 GMT

Use expression like:

=FirstSortedValue(Value, -Date)

when your dimension would be Rep.

Re: Sum Latest Entries

udit_kumar_sana — Mon, 02 Dec 2013 11:54:26 GMT

Hi,

Please find the attachment for the solution.

Thanks & Regards,

Udit

Re: Sum Latest Entries

rustyfishbones — Mon, 02 Dec 2013 11:55:26 GMT

You could try

But you need to ensure your Date Field is formatted correctly

Re: Sum Latest Entries

rustyfishbones — Mon, 02 Dec 2013 11:58:08 GMT

Maybe Try

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

Re: Sum Latest Entries

Mon, 02 Dec 2013 12:02:31 GMT

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)

Re: Sum Latest Entries

Mon, 02 Dec 2013 14:09:03 GMT

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.

Re: Sum Latest Entries

Mon, 02 Dec 2013 14:09:03 GMT

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)