Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Sum Latest Entries

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

DateRepValue
4 NovBruce10
4 NovAndy5
11 NovBruce15
11 NovAndy7
18 NovBruce20
18 NovAndy4

Example of Records:

8 Replies
agni_gold
Valued Contributor II

Re: Sum Latest Entries

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 .

MVP
MVP

Re: Sum Latest Entries

Use expression like:

=FirstSortedValue(Value, -Date)

when your dimension would be Rep.

udit_kumar_sana
Contributor II

Re: Sum Latest Entries

Hi,

Please find the attachment for the solution.

Thanks & Regards,

Udit

rustyfishbones
Honored Contributor II

Re: Sum Latest Entries

You could try

2013-12-02_1153.png

But you need to ensure your Date Field is formatted correctly

rustyfishbones
Honored Contributor II

Re: Sum Latest Entries

Maybe Try

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

Not applicable

Re: Sum Latest Entries

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)


Not applicable

Re: Sum Latest Entries

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.

Not applicable

Re: Sum Latest Entries

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)

Community Browser