topic text sheet object - with date expression in QlikView

text sheet object - with date expression

Wed, 14 Jul 2010 10:06:12 GMT

hi i want to add a text sheet object with a concatenation of text and date. the date is to be returned by an expression that gets the most recent date from a field. I have created a script that works but the date format comes out in numeric format not date.

e.g. my text object script:

='Aged Debtors Report for ' & max( {1} [Sort Date])

returns

Aged Debtors Report for 40367

And it should be

Aged Debtors Report for 08/07/2010

Regards

text sheet object - with date expression

Wed, 14 Jul 2010 10:11:14 GMT

Create a text object and in the text field type in

='Aged Debtors Report for' & today() which will reurn the date everytime it is opened or try

='Aged Debtors Report for' & date(()DD/MM/YYYY)

maybe?

just guessing here sorry

text sheet object - with date expression

Wed, 14 Jul 2010 10:46:01 GMT

Try the following code:

='Aged Debtors Report for ' & date( max( {1} [Sort date]),'DD/MM/YYYY')

text sheet object - with date expression

Wed, 14 Jul 2010 10:58:26 GMT

Hi the folloiwng script worked fine, many thanks.

='Aged Debtors Report for ' & max( Date([Sort Date]))

I was also wondeirng if you might know the answer to the following. If i wanted to add a value from another field called 'additional days' to the max( Date([Sort Date] to get a final date to display in the text object how could i do this?

e.g. if the script

='Aged Debtors Report for ' & max( Date([Sort Date]))

returns

Aged Debtors Report for 08/07/2010

and i wanted toamend script to lets say

='Aged Debtors Report for ' & max( Date([Sort Date])) + additional days

that would hopefully return (if additional days = 4) the following

Aged Debtors Report for 12/07/2010

text sheet object - with date expression

syed_muzammil — Wed, 14 Jul 2010 11:28:05 GMT

Hi,

Try this

='Aged Debtors Report for ' & Date( (num(max([Sort Date]) )+ num([additional days])),'DD/MM/YYYY')

text sheet object - with date expression

Wed, 14 Jul 2010 11:45:15 GMT

This script

='Aged Debtors Report for - ' & Date( (num(max([Sort Date]) )+ num([Days Outstanding])),'DD/MM/YYYY')

returns

Aged Debtors Report for -

Therefore no date is displayed now. The Sort date field is a date and the days outstanding field is a number.

Any suggestions?

text sheet object - with date expression

syed_muzammil — Wed, 14 Jul 2010 12:13:35 GMT

Hi,

Yes that is the reason i used num(). Anyways try removing num from both the feilds.

='Aged Debtors Report for - ' & Date( (max([Sort Date]) + [Days Outstanding]),'DD/MM/YYYY')

='Aged Debtors Report for - ' & Date( (max([Sort Date]) + num([Days Outstanding])),'DD/MM/YYYY')

text sheet object - with date expression

Wed, 14 Jul 2010 12:21:48 GMT

Hi many thanks but both these dont work.

I never knew it would be so difficult adding days to a date field.

Any other suggestions?

text sheet object - with date expression

nathanfurby — Wed, 14 Jul 2010 12:47:56 GMT

I think there may be one set of brackets too many in that last expression. Try:

='Aged Debtors Report for - ' & Date(Max([Sort Date]) + [Days Outstanding],'DD/MM/YYYY')

or perhaps the following if you need the NUM() function before [Days Oustanding]:

='Aged Debtors Report for - ' & Date(Max([Sort Date]) + Num([Days Outstanding]),'DD/MM/YYYY')

text sheet object - with date expression

syed_muzammil — Wed, 14 Jul 2010 12:52:50 GMT

='Aged Debtors Report for - ' & Date( (max([Sort Date]) + num([Days Outstanding]),'DD/MM/YYYY')

yup one bracket was extra. Try this now.

text sheet object - with date expression

Wed, 14 Jul 2010 12:55:38 GMT

Hi both these expression still dont produce the disired result. Any suggestions?

text sheet object - with date expression

nathanfurby — Wed, 14 Jul 2010 13:01:31 GMT

Not without looking at your qvw file

text sheet object - with date expression

Wed, 14 Jul 2010 13:04:20 GMT

I think this syntax will work

=date((today()) + 4,'MM/DD/YYYY')

Anders

text sheet object - with date expression

syed_muzammil — Wed, 14 Jul 2010 13:10:23 GMT

The problem is that the Days Outstanding is a Field. So it is a set which cannot be evaluated with a single value . That is the reason it is not working. If you have a list box of Days Outstanding on your sheet then select a value in that list box. Then you will get the answer in the text box.

If you want to add a single value from the feild Days Outstanding then please clarify what is the value?

text sheet object - with date expression

nathanfurby — Wed, 14 Jul 2010 14:14:11 GMT

You could also try creating a variable to use in the expression. Like Syed said - we would need to now what the Days Outstanding value is all about.