topic Re: Show first year of funding in QlikView

Show first year of funding

Mon, 06 Apr 2015 20:52:51 GMT

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.

Re: Show first year of funding

Anonymous — Mon, 06 Apr 2015 21:37:24 GMT

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)

Re: Show first year of funding

ziadm — Mon, 06 Apr 2015 21:56:06 GMT

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

Re: Show first year of funding

Anonymous — Mon, 06 Apr 2015 22:29:50 GMT

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)

Re: Show first year of funding

Tue, 07 Apr 2015 13:24:22 GMT

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;

Re: Show first year of funding

Anonymous — Tue, 07 Apr 2015 13:32:13 GMT

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;

Re: Show first year of funding

Tue, 07 Apr 2015 14:06:03 GMT

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

];

Re: Show first year of funding

Anonymous — Tue, 07 Apr 2015 14:11:32 GMT

Small addition:

LEFT JOIN (Data) LOAD

Program,

minstring(Year) as FirstYear

RESIDENT Data

WHERE Amounts > 0

GROUP BY Program;

Re: Show first year of funding

Tue, 07 Apr 2015 14:17:02 GMT

It worked, thanks!