Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Have a look at this excellent post around set analysis.
Set Analysis: syntaxes, examples
Cheers,
Antoine
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
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