Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Show Projects that have not received cost in the past 3 months

I've been asked to analyze our cost data to show projects that have received cost in the past 36 months, but not in the last 3 months. I'm sure that those ranges will change and can figure that part out, but I need help with the general concept.


Simplified, my data contain the following columns: Project ID, Date Cost Recorded, Amount


I need to show a simple chart with the list of Project IDs that have cost at any point in the past 36 months, but not in the past 3 months. I feel that a Set Analysis would work, but my mind is warped thinking about the exclusion.


Thanks

3 Replies
Anonymous
Not applicable
Author

Hi,

Have a look at this excellent post around set analysis.

Set Analysis: syntaxes, examples

Cheers,

Antoine

Not applicable
Author

Thanks. I'm just not sure I can get there with my data set. The problem is that if there is no cost in the last three months, there won't be any records as well.

May be turning the problem around I can try loading the data into a different QVW and only load rows that do not have cost in the last three months? The data set is not too large at around 2 million rows. I could even load a table with rows WHERE there IS cost in the last three months and then reload the data into another table that only loads if the project ID is NOT found in the first table. It won't get loaded often so brute force trumps elegance.

Rick

Anonymous
Not applicable
Author

Well, I am thinking of the following:

On load, create a small table that will identify the maximum date for cost received.

T1:

LOAD

Project ID,

Max(Date Cost Recorded) as LatestDate

FROM source

GROUP BY Project ID;


Then, evaluate LatestDate against today's date to identify the Maxdates that are within your n months.

T2:

LOAD

*,

IF(Today() - LatestDate > 3*30, 'Greater than 3 months', 'Less than 3 months') as FLag_

Resident T1;

DROP TABLE T1;

You have now the information that is required.


Going back to your original information:


Map1:

LOAD

Project ID,

Flag_

Resident T2;

DROP TABLE T2;


T3:

LOAD

Project ID,

ApplyMap('Map1', Project ID) as Flag2,

Date Cost Recorded,

Amount

FROM Source;


This way, you could get what you are asking for.


Hope this helps,


Antoine