Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV Community,
I tried to get a textbox expression working, but i failed.
I have attached a sample qvw with the problem + description
Script:
Metrics:
LOAD
CalendarMonthAndYear,
empty,
Kritikalität,
Kritikalität_Bewertung,
Metric,
Nullvalues,
SubMetric,
UseCaseCluster,
UseCaseDescriptor,
UseCaseNumber,
UseCaseRelevance,
Value,
Value_95%
FROM
[Metrics.xls]
(biff, embedded labels, table is [Sheet1$]);
Mastercalendar:
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 CalendarWeekDayAndYear,
Num(Week(TempDate),'00') & '-' & Year(TempDate) as CalendarWeekAndYear,
//Week(TempDate) & '-' & Weekyear(TempDate) AS CalendarWeekAndYear,
//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,
if(WeekDay(TempDate)='So' and Day(TempDate)<='07','FirstMonthSunday','') as FirstMonthSunday
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Dimension linechart:
=if(MonthDiff>=$(vZeitraum12Monate) and MonthDiff<=$(vAktuellerMonat),MonthAsNumber)
Expression 1 (linechart)
sum(aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value), MonthAsNumber,UseCaseDescriptor))/
(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor)))
Expression 2 (linechart)
RangeSum(Above((sum({<UseCaseRelevance={'1'},Metric={'Availability'}>}aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value), MonthAsNumber,UseCaseDescriptor))/
(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor)))),0,RowNo()))/
RangeSum(Above(if((sum({<UseCaseRelevance={'1'},Metric={'Availability'}>}aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value), MonthAsNumber,UseCaseDescriptor))/
(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor))))>0, 1, 0),0,RowNo()))
Expression 3 Hidden (linechart)
avg({1}MonthDiff/MonthDiff)
Expression Textbox 1
=num((sum({<[MonthDiff]={[$(vAktuellerMonat)]},UseCaseRelevance={'1'},Metric={'Availability'}>}aggr(min({<[MonthDiff]={[$(vAktuellerMonat)]},UseCaseRelevance={'1'},Metric={'Availability'}>}Value), MonthAsNumber,UseCaseDescriptor))/
(count({$<[MonthDiff]={[$(vAktuellerMonat)]},UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor)))),'#.##0,#%')
Expression textbox2: THIS IS THE EXPRESSION I NEED TO GET WORKING
=num(rangesum(above( sum(aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value),MonthAsNumber,UseCaseDescriptor))/
aggr(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor)),MonthAsNumber),0,RowNo()))
/
(count(Aggr(sum(aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value), MonthAsNumber,UseCaseDescriptor))/
(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor))),MonthAsNumber))),'##.##0,#%')
So this is my setup. The linechart works fine and the textbox 1, which shows the monthvalue of Expression 1(linechart) in a textbox works also fine. But textbox 2 with the monthvalue of Expression 2 (linechart) is not working.
I really appreciate every hint
Thx for helping!
any ideas?
Can you post textbox expr? Expected output?
The expression looks like this:
num(rangesum(above( sum(aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value),MonthAsNumber,UseCaseDescriptor))/
aggr(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor)),MonthAsNumber),0,RowNo()))
/
(count(Aggr(sum(aggr(min({<UseCaseRelevance={'1'},Metric={'Availability'}>}Value), MonthAsNumber,UseCaseDescriptor))/
(count({$<UseCaseRelevance={'1'},Metric={'Availability'}>}DISTINCT(UseCaseDescriptor))),MonthAsNumber))),'##.##0,#%')
and the expected value should be 86,0% for 03/15
As long as there are no missing values for a month everything is ok. but when there is, for example, no value for actual month, the expression is not working.
Rangesum(Above()) is Chart range function which is not for Textbox
But you can use a rangesum(above()) expression also in a textbox by using the aggr()function. Its working for me with a lot of textboxes in my app.