Sum on last available date of each month

[#1]

We are running a data warehouse which contains a measure date, a resource name and a workflow count. This data warehouse is updated every night, and we are cleaning up data older than a week, only keeping the data of mondays. This results in the following (example) table:

Meaure Date Resource Name   Workflow Count
17-2-2014    Resource A           10  
17-2-2014    Resource B           12
24-2-2014    Resource A           12
24-2-2014    Resource B           12
3-3-2014      Resource A           13
3-3-2014      Resource B           11
10-3-2014    Resource A           14
10-3-2014    Resource B           10
17-3-2014    Resource A           12
17-3-2014    Resource B           9
24-3-2014    Resource A           11
24-3-2014    Resource B           8
31-3-2014    Resource A           11
31-3-2014    Resource B           8
1-4-2014      Resource A           12
1-4-2014      Resource B           6
2-4-2014      Resource A           14
2-4-2014      Resource B           7
3-4-2014      Resource A           12
3-4-2014      Resource B           10
4-4-2014      Resource A           11
4-4-2014      Resource B           9
5-4-2014      Resource A           9
5-4-2014      Resource B           12
6-4-2014      Resource A           11
6-4-2014      Resource B           11

I want to build a graph on this table, only showing the sum of the workflow on the last available measure date of each month. Above table should result in:
24-2-2014: 24
31-3-2014: 19
6-4-2014: 22

Is this possible using set analysis, and which expression could I use?

Thanks in advance.

Het bericht is bewerkt door: Andreas Markus

Het bericht is bewerkt door: Andreas Markus

[#2]

I'm afraid I'm still not able to get it right in my QlikView.

What would also work, is if I could flag every last monday of the month in my master calendar. My master calendar script is as follows:

LOAD
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
RESIDENT Facts;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));
LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));
LET vToday = $(vMaxDate);

//*****Temp Calendar*****
TempCal:
LOAD
DATE($(vMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****
MasterCalendar:
LOAD
TempDate AS Date,
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) AS Day,
WEEKDAY(TempDate) AS WeekDay,
'Q' & CEIL(MONTH(TempDate)/3) AS Quarter,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,
INYEARTODATE(TempDate,$(vToday),0) * -1 AS CurYTDFlag,
INYEARTODATE(TempDate,$(vToday),-1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;

Any idea how I could accompish this?