Textbox Expression

Frank_Hartmann — Mon, 20 Apr 2015 12:09:19 GMT

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!

Re: Textbox Expression

Frank_Hartmann — Tue, 21 Apr 2015 08:03:39 GMT

any ideas?

Re: Textbox Expression

anbu1984 — Tue, 21 Apr 2015 08:24:10 GMT

Can you post textbox expr? Expected output?

Re: Textbox Expression

Frank_Hartmann — Tue, 21 Apr 2015 08:33:15 GMT

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.

Re: Textbox Expression

anbu1984 — Tue, 21 Apr 2015 10:48:45 GMT

Rangesum(Above()) is Chart range function which is not for Textbox

Re: Textbox Expression

Frank_Hartmann — Tue, 21 Apr 2015 11:53:40 GMT

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.

topic Re: Textbox Expression in QlikView

Textbox Expression

Re: Textbox Expression

Re: Textbox Expression

Re: Textbox Expression

Re: Textbox Expression

Re: Textbox Expression