Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Below is my caluclated Dimension which works for current month.
=If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<600,'0-600',
If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<800,'600-800',
If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1000,'800-1000',
If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1500,'1000-1500',
''))))
If i want to do it for Previous month , how can i do that
Regards
This is one hell of a calculated dimension.
Are you sure you can not do this in script instead? Calculated dimensions are not good for performance. Setting a flag in script might make this even a lot easier to do.
add the function addmonths around your current month definition
now. Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD'
new for previous month: addmonths(Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD',-1)
or use inner Definition Date(addmonths(MonthStart(Max(ALT1)),-1), 'YYYY-MM-DD'
maybe try like this please:
=If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<600,'0-600',
If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<800,'600-800',
If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1000,'800-1000',
If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1500,'1000-1500',
''))))