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
Please explain more on the final result it is not clear please clear it more.
and
Where is 25-2-2014: 24 this date come it is not in sample.
Sorry, my mistake. I've edited my original post.
I need the sum of the workflow on the last available date of each month.
Take ,
[Meaure Date] as dimension
Expression>> sum({$<[Meaure Date]={$(=max([Meaure Date]))}>}[Workflow Count])
PFA,
Sorry for the late response. I still prefer to do it via set analysis, but it still doesn't fully work. When I use sum({$<[Meaure Date]={$(=max([Meaure Date]))}>}[Workflow Count]), it says "Error: Error in set modifier ad hoc element list: ',' or ')' expected"
I can solve this as follows: "=Sum({$<[Date] = {'$(=Max([Date]))'}, [IsWorkflow] = {1}>}[Workflow Count])", but than it only displays the data from the last date of the last month. Not the last date per month.
Any idea how this can be solved?
You could try
AGGR(Sum({$<Date ={'$(=DATE(MAX(Date)))'}>}[Workflow Count]),Month)
Thanks, but somehow it still only gives me the last date of the last month.
or maybe try
FIRSTSORTEDVALUE
May be like attached sample;
Exp:
=FirstSortedValue(Aggr(Sum([Workflow Count]),[Measure Date]), -Aggr([Measure Date],[Measure Date]))