Setting a variable within the script

[samvile18]

Afternoon all,

I'm just wondering if the following is at possible?

I'm trying to set the variable vMaxDate to the maximum date from a list of measures within a QVD, the list of measures is:

KPI             UploadDate

Actual         31/07/2012

Budget        31/07/2012

FTE            31/07/2012

Leavers       04/07/2012

Vacancies   29/07/2012

I want vMaxDate to be the maximum value for the KPI Actual....can anyone out there help please?

Any help is greatly appreciated.

[jagan]

Hi,

First load the data into the QVW file, and then create a variable in Settings - Variable Overview and create new variable with

=Date(Max({1}UploadDate))

as expression this will always fetch the maximum date. i.e, (31/07/2012) irrespective of selections.

Hope this helps you.

Regards,

Jagan.

[samvile18]

This is what I thought it might look like, the problem I have though is I only want to use the value that's associated with the Actual KPI. The reason for this is because there may be another KPI with a later date that I don't want to use.

[gandalfgray]

hi

then you could create a temporary table in your load script:

tmp:

Load Max(UploadDate) As max_actual_upload_date

Resident <name_of_table>

Where KPI='Actual';

Let vMaxDate=Peek('max_actual_upload_date');

Drop Table tmp;

[samvile18]

This is what I do at the moment, the issue I have is when there is another KPI with a different date.

e.g.     Actual    31/07/2012

          Budget    06/08/2012

If I use the max command I end up setting the variable value as 06/08/2012, I don't want this to happen. I only want the variable to be the value of the Actual KPI.

[samvile18 · Accepted solution]

Hi chaps,

Just to let you know, I figured it out...like everything I was thinking too complex and all I needed to do was add a where clause to the temp table:

TempUploadMonth:

Load Distinct

Max(UploadDate) as MaxDate

Resident UploadMonth

Where KPI='Actual';

Thanks for taking the time to look at this with me