Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
In my expression, i am adding this code,
Count({$<[Cause Code]={'Access Issue'},InciMonth = {">=$(=Month(MonthStart(Max(Approved))-365))<=$(=Month(MonthEnd(Max(Approved))-1))"},InciYear=,InciMonth=>}[Task])
Here i am counting the Task with the Cause Code ='Access Issue'.
Approved is the Date column . InciYear & InciMonth is the Year & Month Respectively.
The values i am getting correctly, but the x-axis is not changing based on my month selection. Can you please help me out.
Thanks,
Sasikumar.R
a sample app would help, but I guess you need to create a calculated dimension with similar set analysis
i think you should create a mastercalendar.
1. Mastercalendar in script:
LET vZeitraum12Monate = '=$' & '(vAktuellerMonat)' & '-11';
LET vZeitraum6Monate = '=$' & '(vAktuellerMonat)' & '-5';
LET vZeitraum3Monate = '=$' & '(vAktuellerMonat)' & '-2';
LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar:
LOAD
(year(TempDate)*12 + Month(TempDate)) - (year($(vDateMin))*12 + Month($(vDateMin))) as MonthNumber,
(year(TempDate)*12 + Month(TempDate)) - (year($(vDateToday))*12 + Month($(vDateToday))) as MonthDiff,
'AK_' & Date(TempDate,'YYYYMMDD') AS Referal_Date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Num(Month(TempDate),00) AS MonthAsNumber,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear,
Num(Date(TempDate,'YYYYMMDD')) AS CalendarYearMonthDay,
Year(TempDate)&Num(Month(TempDate),00) AS CalendarYearMonth,
Num(Month(TempDate),00)&'/'&Date(TempDate,'YY') AS DateDimension,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CalendarCurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS CalendarLastYTDFlag
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
--------------------------------------------------------------------------------------------------------------------
connect Referal_Date from Mastercalendar with your table, something like:
'AK_' & Date(Datum,'YYYYMMDD') AS Referal_Date or
'AK_' & Date(Date#(Datum, 'YYYY/MM'),'YYYYMMDD') AS Referal_Date,
--------------------------------------------------------------------------------------------------------------------
2. create diagramm with following dimension:
=if(MonthDiff>=$(vZeitraum12Monate) and MonthDiff<=$(vAktuellerMonat),MonthAsNumber)
--------------------------------------------------------------------------------------------------------------------
3. chart formula (invisible style):
=avg({1}MonthDiff/MonthDiff)
--------------------------------------------------------------------------------------------------------------------
4. Sort: Formula: = MonthNumber
-------------------------------------------------------------------------------------------------------------------
5. Your chartexpression: e.g. sum(sales)
-------------------------------------------------------------------------------------------------------------------
6. create a Slider with vAktuellerMonat as Variable, Minvalue= -11, Maxvalue= 0 Interval: 1
--------------------------------------------------------------------------------------------------------------------
Thats it!
cheers