Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?