An issue with the expression in a chart

[#1]

Hi,

LOAD INCIDENT_NUMBER,

    INC_CREATION_DATE,

    INC_REPORTING_PERSON as REPORTING_PERSON,

    INC_TITLE

FROM \\REPORTS\QlikView\Data\V_INCIDENTS.qvd

(qvd);

LOAD  RFC_NUMBER,

    RFC_CREATION_DATE,

    RFC_REPORTING_PERSON as REPORTING_PERSON,

    RFC_TITLE,

    RFC_CLOSED_DATE,

    RFC_CLOSED_YEAR

FROM \\REPORTS\QlikView\Data\V_RFCS.qvd

(qvd);

I've created a report that contains two table charts. Each table has an expression.
The result of the left chart is correct, the problem i have with the right chart is that  I added an expression but i'm affraid there is an issue with it.

The result of the right box should only be available when

1) The requestor in both charts are the same

2) if the name is the same then list all records where the creation date is within a time span of 50 days.

If(REPORTING_PERSON, (INC_CREATION_DATE > RFC_CLOSED_DATE and INC_CREATION_DATE < (RFC_CLOSED_DATE + 50)))

I've tried: If(INC_REPORTING_PERSON = RFC_REPORTING_PERSON, (INC_CREATION_DATE > RFC_CLOSED_DATE and INC_CREATION_DATE < (RFC_CLOSED_DATE + 50)))

But in the last case when I reload, my qlikview crashes.
Does anyone know what I'm doing wrong?

Regards

Fred

[swuehl]

You already have linked the two tables by REPORTING_PERSON, so I think there is no need to check for the same person, just use as expression:

=If(INC_CREATION_DATE > RFC_CLOSED_DATE and INC_CREATION_DATE < (RFC_CLOSED_DATE + 50), 1,0)

Is this what you want?

[#3]

That's what I did at first as well, but the problem is that the right box is showing records where same requestor doesn't even exist in the left box. It should only show the records where requestor is the same in both cases

[swuehl]

Could you give an example (e.g. Requester name) for this?

[#5]

Thanks for your help.

In the Right box I used the following as expression:

=If(INC_CREATION_DATE > RFC_CLOSED_DATE and INC_CREATION_DATE < (RFC_CLOSED_DATE + 14), 1,0)

In the overview incidents we have Requester: 'Beauduin Tim'

-> In the overview renewal hardware (right box)  that name isn't even listed. So that isn't correct.

In the overview Renewal Hardware box we have requester 'PIRE Delphine'

-> in the right box records are available for that name. This is correct

[swuehl]

I see...

Your left box is already filtered, I think you need to apply the filter also to the right box:

=If( RFC_TITLE like '*renewal*hardware*' and RFC_CLOSED_YEAR = '2011',if(INC_CREATION_DATE > RFC_CLOSED_DATE and INC_CREATION_DATE < (RFC_CLOSED_DATE + 14),1,0))

Now I get only four records back, including PIRE Delphine.

Is this what you want?

[swuehl · Accepted solution]

This is probably more performant and is returning some more records, to my surprise.

=Only ({<RFC_TITLE={"*renewal*hardware*"},RFC_CLOSED_YEAR={2011},INCIDENT_NUMBER ={"=INC_CREATION_DATE > RFC_CLOSED_DATE and INC_CREATION_DATE < (RFC_CLOSED_DATE + 14)"} >} INC_TITLE)

[#8]

Exactly what i wanted. Many thanks !!