Show first year of funding

[#1]

Hi everyone,

I have a table that shows the top 5 projects by value. How can I add an expression that shows the first [Year] that has funding? I want this calculation to ignore whatever Years I have selected, and also these Years are in text with 'FY2015', 'FY2016', rather than just 2015, 2016. So I think any solution that would use min will use minstring instead.

[Anonymous]

Would you want to sort the rows in your table based on the funding is there or not. Can you provide some more details or some sample data? Ignoring year selection, you can use set analysis i.e. sum({<Year=,>} value)

[ziadm]

I would recommend using the master calendar and flag the year that as funding in the master calendar. 

During the load check flag the years with funding.  Once you have this flagged then you may filter only years with flagged.  This is an idea.

Regards

Ziad

[Anonymous]

it is min(Year) per project.  If it is in the script:

LOAD (or SELECT)

     Project.

     min(Year) as FirstYear

FROM (or RESDIENT)

GROUP BY Project;

If it is on the front end in a table, and Project is a dimension, the expression will be just min(Year)

[#5]

Hi Michael, thank you so much, this definitely seems to be the solution. However, would you mind helping me with the script? Using a simple INLINE statement, how should I correct the below?

Data:

Load * INLINE [

    Program, Year, Amounts

    t, FY2010, 7

    u, FY2011, 15

    v, FY2012, 10

    v, FY2013 20

    ];

LOAD

Program,

minstring(Year) as FirstYear

GROUP BY Program,

FROM Data;

[Anonymous]

Here it is:

Data:
Load * INLINE [
    Program, Year, Amounts
    t, FY2010, 7
    u, FY2011, 15
    v, FY2012, 10
    v, FY2013, 20
    ];

LEFT JOIN (Data) LOAD
Program,
minstring(Year) as FirstYear
RESIDENT Data
GROUP BY Program;

[#7]

Hi Michael, sorry but there's actually a small wrinkle in my real data that I forgot to include in my example. My Year column always has all years FY2010-FY2012 for each program, even if Amounts = 0. So how do I tell the script to flag FirstYear as the first year that actually has Amounts? E.g.:

Data:

Load * INLINE [

    Program, Year, Amounts

    t, FY2010, 0

    t, FY2011, 10

    t, FY2012, 15

    u, FY2010, 15

    u, FY2011, 0

    u, FY2012, 5

    v, FY2010, 0

    v, FY2011, 5

    v, FY2012, 10

    ];

[Anonymous · Accepted solution]

Small addition:

LEFT JOIN (Data) LOAD

Program,

minstring(Year) as FirstYear

RESIDENT Data

WHERE Amounts > 0

GROUP BY Program;

[#9]

It worked, thanks!