topic Re: Text Box - Value link to an expression in QlikView

Text Box - Value link to an expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I need help with an expression in a text box. Attached herewith is data relating to Calls and the date the Call was created.

I want an expression to tell me which is the oldest call? The answer should be IR1086. How do i do this? I have worked out the number of days the oldest call is, which is 197 days.

Please can you assist.

Kind regards

Nayan

Re: Text Box - Value link to an expression

Frank_Hartmann — Wed, 22 Mar 2017 10:44:42 GMT

try this:

date(num(today())-(max(today() - [Created Date])))

hope this helps

Re: Text Box - Value link to an expression

Anil_Babu_Samineni — Wed, 22 Mar 2017 10:48:54 GMT

May be use this?

=Only({<[Created Date] = {'$(=Max([Created Date]))'}>}Call)

Re: Text Box - Value link to an expression

awhitfield — Wed, 22 Mar 2017 10:57:12 GMT

Hi Nayan,

use the following:

=Only({<[Created Date] = {'$(=Min([Created Date]))'}>}Call)

as you are after the OLDEST date

HTH

Andy

Re: Text Box - Value link to an expression

yoganantha321 — Wed, 22 Mar 2017 10:59:51 GMT

Hi Nayan,

As Andrew mention use the following expression given by andrew:

=Only({<[Created Date] = {'$(=Min([Created Date]))'}>}Call)

It works perfectly

Re: Text Box - Value link to an expression

stabben23 — Wed, 22 Mar 2017 11:00:20 GMT

Or by using Firstsortedvalue:

FirstSortedValue(Call,[Created Date],1)

Re: Text Box - Value link to an expression

sunny_talwar — Wed, 22 Mar 2017 11:02:18 GMT

May be this:

=FirstSortedValue(Call, [Created Date])

Re: Text Box - Value link to an expression

Anonymous — Wed, 22 Mar 2017 11:24:37 GMT

Hi All

Thank you for your reply. It was helpful. I have additional conditions ,where the

"Service Level = "Active".
Call Type = "Incidents"

See new data attached and the model.

How do I get the oldest Call number?

Kind regards

Nayan

Re: Text Box - Value link to an expression

sunny_talwar — Wed, 22 Mar 2017 11:27:54 GMT

May be this:

=FirstSortedValue({<[Call Type] = {'Incidents'}, Resolved_SLA = {'Active'}>}Call, [Created Date])

Re: Text Box - Value link to an expression

sunny_talwar — Wed, 22 Mar 2017 11:28:35 GMT

or this:

=Only({<[Created Date] = {'$(=Min([Created Date]))'}, [Call Type] = {'Incidents'}, Resolved_SLA = {'Active'}>}Call)

Re: Text Box - Value link to an expression

yoganantha321 — Wed, 22 Mar 2017 11:28:46 GMT

Hi nayan,

try with this,

=Only({<[Created Date] = {'$(=Min([Created Date]))'},"Service Level={'Active'},Call Type={'Incidents'}>}Call)

Re: Text Box - Value link to an expression

Anonymous — Wed, 22 Mar 2017 11:38:42 GMT

Thank you all for the responses to my second question.

One quick question. If there are 2 Calls that have the same oldest date, the Call numbers wont show. Is that correct or is there a way to solve this?

Kind regards

Nayan

Re: Text Box - Value link to an expression

sunny_talwar — Wed, 22 Mar 2017 11:42:41 GMT

Which of the two would you want to see? If Randomly any... you can try this

=FirstSortedValue(DISTINCT {<[Call Type] = {'Incidents'}, Resolved_SLA = {'Active'}>}Call, [Created Date])

If both, then this

=Concat({<[Created Date] = {'$(=Min([Created Date]))'}, [Call Type] = {'Incidents'}, Resolved_SLA = {'Active'}>}Call, ', ')

Re: Text Box - Value link to an expression

awhitfield — Wed, 22 Mar 2017 12:02:42 GMT

Hi Nayan,

this will work for multiple values:

=Concat({<[Created Date] = {'$(=Min([Created Date]))'}>}Call, ' ')

Andy

Re: Text Box - Value link to an expression

Anonymous — Wed, 22 Mar 2017 12:16:29 GMT

Thank you Sunny, it worked