topic Re: New Field Help in QlikView

New Field Help

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need to build the flowing graph:

The filed i have also includes other dates that i wont be using (like 10/1/2016 and so on), I only need to count late 30, late 60 and late 90.

What is the best way to create a new field using only "late" dates?

Please advise.

Re: New Field Help

sunny_talwar — Fri, 27 May 2016 16:26:39 GMT

Using if statement, may be along these lines:

If(Date - Today() > 90, '90 Days Late',

If(Date - Today() > 60, '60 Days Late',

If(Date - Today() - 30, '30 Days Late'))) as Late_Flag

Re: New Field Help

Peter_Cammaert — Fri, 27 May 2016 16:26:39 GMT

Maybe there is no need to create a new field. Use set analysis to restrict dimension values to three specific values only, like in:

=count({<[Expected Remediation Date] *= {'30 Days LATE', '60 Days LATE', '90 Days LATE'}>} WhateverYouLikeToCount)

Note: in set analysis, *= is the combined intersection assignment operator.

Peter

Re: New Field Help

Fri, 27 May 2016 16:38:38 GMT

30 60 and 90 is already in the table, i do not need to define from today. how would i write without it?

Re: New Field Help

Peter_Cammaert — Fri, 27 May 2016 17:41:36 GMT

Nevermind the warnings from the syntax checker. The syntax checker is broken; it doesn't understand compound operators like *= but they do work!

Good luck,

Peter

Re: New Field Help

Fri, 27 May 2016 18:50:42 GMT

Hi Peter,

would set analysis work if I add another conditions?

=count({<[Expected Remedition Date]*= {'30 Days LATE','60 Days LATE', '90 Days LATE'}>}{<[Plan_2016]*= {1}>}{<[Vendor Type]*= {'Quality Systems}>}[Expected Remedition Date])

Re: New Field Help

Fri, 27 May 2016 18:51:26 GMT

my first bracket is highlighted red

Re: New Field Help

sunny_talwar — Fri, 27 May 2016 18:52:27 GMT

Try this:

=Count({<[Expected Remedition Date] *= {'30 Days LATE', '60 Days LATE', '90 Days LATE'}, [Plan_2016] *= {1}, [Vendor Type] *= {'Quality Systems}>}[Expected Remedition Date])

Re: New Field Help

Fri, 27 May 2016 18:54:10 GMT

first bracket still red

Re: New Field Help

sunny_talwar — Fri, 27 May 2016 18:57:22 GMT

Which bracket are you talking about? Can you highlight it with Red in your next response?

Re: New Field Help

Fri, 27 May 2016 18:59:09 GMT

Re: New Field Help

sunny_talwar — Fri, 27 May 2016 19:00:23 GMT

We have a missing single quote here:

=Count({<[Expected Remedition Date] *= {'30 Days LATE', '60 Days LATE', '90 Days LATE'}, [Plan_2016] *= {1}, [Vendor Type] *= {'Quality Systems'}>}[Expected Remedition Date])

Re: New Field Help

Fri, 27 May 2016 19:01:53 GMT

Thank you for all the help in the past few days!

Re: New Field Help

Tue, 31 May 2016 13:31:04 GMT

Hi Peter

What will be the operator if we want to count values other than the one listed for remediation date:

=count({<[Expected Remediation Date] *= {'30 Days LATE', '60 Days LATE', '90 Days LATE'}>} WhateverYouLikeToCount)

I looked old posts that said we can use -= or =- but none of that worked.

Re: New Field Help

sunny_talwar — Tue, 31 May 2016 15:46:11 GMT

May be this:

=Count({<WhateverYouLikeToCount = e({<[Expected Remediation Date] = {'30 Days LATE', '60 Days LATE', '90 Days LATE'}>})>} WhateverYouLikeToCount)

Re: New Field Help

Tue, 31 May 2016 16:21:43 GMT

Thanks a lot.

I figured out that the reason why -= was not working was because of the nulls in the data. Those records are getting excluded.

I tried your syntax above and got the same result. Is there a way to make sure that the records with null values also get counted?