Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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 = ??