topic Re: Subtract Date - Problem with selection in QlikView

Subtract Date - Problem with selection

Anonymous — Fri, 10 Nov 2017 09:56:27 GMT

Goodmorning to everyone,

I'm here with a problem without a solution in the community (at least, I didn't find a solution!).

I have a fact table that contain all the operation about a document. The fact is like

Id_document	Id_operation	Date	Month
xxxxyyyy	1	31/10/2017	October
xxxxyyyy	2	01/11/2017	November
eeeeffff	1	07/11/2017	November
eeeeffff	2	09/11/2017	November

I have to calculate the mean time between the operation 1 and 2.

=sum(aggr(sum({<Id_operation={'2'}>} Data)-sum({<Id_operation={'1'}>} Data),Id_document))/count({<DISTINCT Id_document)

For this example, the mean value is 1.5 days.

If i select one month (for example november), the value isn't correct (01/11/2017).

How can i consider only the document with all the operation in the month "November"?

Alternatively, how can i consider also the record with month "October" that have an operation in "November"?

I have to add two more columns (MonthID1 and MonthID2) and move the selection on that field?

Thanks in advice,

Best Regards from Italy!

Paolo

Re: Subtract Date - Problem with selection

Colin-Albert — Fri, 10 Nov 2017 12:50:58 GMT

Are the dates loaded as text or are they correctly loaded as dual() fields?

I ask this because there are spaces in front of the Date & Month for 07/11/2017 in the data you posted.

Perhaps these posts will help you get the dates loaded correctly.

Why don’t my dates work?‌

Get the Dates Right

Also it may help you identify the issue if you add test expresions to your table for

sum({<Id_operation={'1'}>} Data) , sum({<Id_operation={'2'}>} Data) and count({<DISTINCT Id_document) so you can check those values are correct.

Re: Subtract Date - Problem with selection

Anonymous — Mon, 13 Nov 2017 08:22:13 GMT

The dates are loaded correctly as Date, here i posted only a few example wrote for example.

The problem is about Selection. When any Date and any Month aren't selected, the value is correct.

When one month is selected, the value isn't correct.

As you can see (using the example I posted), if i select "November" i have :

(09/11/2017 +01/11/2017 - 07/11/2017) / 2 = ??