topic Re: Text Box Expression in QlikView

Text Box Expression

upaliwije — Thu, 16 Oct 2014 15:36:37 GMT

In a text box I have following expression and is working perfectly

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=Only([FYear]))"}>}CLAIM_NO),'#,##0.')

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=Only([FYear])-1)"}>}CLAIM_NO),'#,##0.')

But When I modify the expression with -1 it is not working.

My FYear list box has following data

2011/2012

2012/2013

2013/2014

Is any thing wrong with my expression. Pls help

Re: Text Box Expression

simondachstr — Thu, 16 Oct 2014 15:38:48 GMT

Your FYear list box will be interpreted as string.. substracting 1 from it will result to null.

Instead of only(), you should be using the FirstSortedValue() functionality.

Re: Text Box Expression

upaliwije — Thu, 16 Oct 2014 15:43:00 GMT

Thanks

I don't understand it much . Can u modify me expression and let me know pls

Re: Text Box Expression

simondachstr — Thu, 16 Oct 2014 15:45:28 GMT

Try

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=FirstSortedValue([FYear],-[FYear],1))"}>}CLAIM_NO),'#,##0.')

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=FirstSortedValue([FYear],-[FYear],2))"}>}CLAIM_NO),'#,##0.')

Re: Text Box Expression

MK_QSL — Thu, 16 Oct 2014 15:51:12 GMT

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=(Left(Only(FYear),4)-1) & '/' & (Right(Only(FYear),4)-1))"}>}CLAIM_NO),'#,##0.')

Re: Text Box Expression

upaliwije — Thu, 16 Oct 2014 16:09:00 GMT

Thanks all

Re: Text Box Expression

simondachstr — Thu, 16 Oct 2014 16:15:54 GMT

That expression is very calculation intensive. A string is being split apart and then concatenated back into one.

Re: Text Box Expression

upaliwije — Thu, 16 Oct 2014 16:19:18 GMT

Tks,

But your one does not give desired results

Re: Text Box Expression

simondachstr — Thu, 16 Oct 2014 16:54:31 GMT

I agree it doesn't work, First Sorted Value needs a numeric value to sort on which is not the case in FYear.

Instead, I recommend you to create an additional numeric field in the script (maybe FY_ID using AutoNumber()) and filter your set analysis based on that.

Re: Text Box Expression

upaliwije — Fri, 17 Oct 2014 03:44:28 GMT

Thanks for your continuous help.

Sorry for the delay in replying. If you can let me know how to create Autono with an example I would appreciate very much. I am attaching my script my FYear Calender script and pls incorporate autono script into same.

Set vFM = 9 ; // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(MONTH, fMonth) as FMonth, // Dual fiscal month

Load YEAR + If(MONTH>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(MONTH-$(vFM), 12)+1 as fMonth,

* ;

load

POL_PERIOD_TO,

Day(POL_PERIOD_TO) AS DAY,

Date(POL_PERIOD_TO) AS DATE,

Week(POL_PERIOD_TO) AS WEEK,

Year(POL_PERIOD_TO) AS YEAR,

Month(POL_PERIOD_TO) As MONTH,

Month(POL_PERIOD_TO) &'-'& week(POL_PERIOD_TO) As Month_Week;

Load Date(TRN_MinDate + IterNo() -1 ) AS POL_PERIOD_TO While (TRN_MinDate + IterNo() - 1) <= Num(TRN_MaxDate);

Load

Min(POL_PERIOD_TO) AS TRN_MinDate,

Max(POL_PERIOD_TO) AS TRN_MaxDate

RESIDENT Link_Table;