topic Re: Help with set analysis in QlikView

Help with set analysis

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

Hello,

I have this expression:

=SUM({<

P_DATE_PRODUCTION = {'<=$(DATE_EXTRACTION)'},

RIGHT(P_DATE_OUVERTURE,2) = {'=RIGHT($(ANNEE_EXTRACTION),2)'}

>}P_MONTANT_PRODUCTION)

When I delete RIGHT(P_DATE_OUVERTURE,2) , the error is taken.

But I can't handle without the condition in red.

Any help please?

Regards.

Re: Help with set analysis

jonathandienst — Fri, 23 Feb 2018 07:05:16 GMT

The LHS of set expressions must be a physical field name, so you cannot use Right(...) there. Options are

create a derived field using

RIGHT(P_DATE_OUVERTURE,2) as Derived

and use this field in the set expression

use this expression

P_DATE_OUVERTURE = {"=$(=RIGHT(P_DATE_OUVERTURE,2) = RIGHT($(ANNEE_EXTRACTION),2)"}

(success will depend on the data structure in your document)

Re: Help with set analysis

qlik4asif — Fri, 23 Feb 2018 07:09:05 GMT

You can create a field in backend with RIGHT(P_DATE_OUVERTURE,2) as XXXXX,

Use this field the expression

=SUM({<

P_DATE_PRODUCTION = {'<=$(DATE_EXTRACTION)'},

XXXXX = {'=RIGHT($(ANNEE_EXTRACTION),2)'}

>}P_MONTANT_PRODUCTION)

Re: Help with set analysis

aminehaddar — Fri, 23 Feb 2018 07:16:21 GMT

Thanks for your answer.

I edited like this:

=SUM({<

P_DATE_PRODUCTION = {'<=$(DATE_EXTRACTION)'},

P_DATE_OUVERTURE = {'=$(RIGHT(P_DATE_OUVERTURE,2)

=RIGHT($(ANNEE_EXTRACTION),2)'}

>}P_MONTANT_PRODUCTION)

The expression is ok.

But when I press the apply button, I find this error: error in set modifier ad hoc element: ; or ') , strange no?

Regards

Re: Help with set analysis

aminehaddar — Fri, 23 Feb 2018 07:18:01 GMT

I tried it before posting my problem.

But the system doesn't accept a field in backend in the place of the attribut.

Regards

Re: Help with set analysis

qlik4asif — Fri, 23 Feb 2018 07:26:06 GMT

If P_DATE_OUVERTURE is a field

Then you can create new field in backend

RIGHT(P_DATE_OUVERTURE,2) as XXXXX,

Re: Help with set analysis

aminehaddar — Fri, 23 Feb 2018 07:32:32 GMT

syntax error bro

Re: Help with set analysis

qlik4asif — Fri, 23 Feb 2018 09:07:10 GMT

can u share qvw or code which you used

Re: Help with set analysis

jonathandienst — Fri, 23 Feb 2018 11:01:08 GMT

Corrected expression

SUM({<

P_DATE_PRODUCTION = {'<=$(DATE_EXTRACTION)'},

P_DATE_OUVERTURE = {'=$(=RIGHT(P_DATE_OUVERTURE, 2)

=RIGHT(ANNEE_EXTRACTION, 2))'}

>} P_MONTANT_PRODUCTION)

Re: Help with set analysis

sunny_talwar — Fri, 23 Feb 2018 12:40:54 GMT

Try this may be

=Sum({<P_DATE_PRODUCTION = {"<=$(DATE_EXTRACTION)"}, P_DATE_OUVERTURE = {"=RIGHT(P_DATE_OUVERTURE, 2)

= RIGHT($(ANNEE_EXTRACTION), 2)"}>} P_MONTANT_PRODUCTION)

2 things I changed

1) Remove dollar sign expansion on top of RIGHT function for your P_DATE_OUVERTURE

2) Use double quotes for search string

Quotes in Set Analysis

Re: Help with set analysis

pablolabbe — Wed, 28 Feb 2018 17:29:40 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others