Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_Hartmann
Master II
Master II

Textbox Expression

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!

5 Replies
Frank_Hartmann
Master II
Master II
Author

any ideas?

anbu1984
Master III
Master III

Can you post textbox expr? Expected output?

Frank_Hartmann
Master II
Master II
Author

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.

anbu1984
Master III
Master III

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

Frank_Hartmann
Master II
Master II
Author

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.