Hi, everyone!
I’m using Eazybi to generate a report where I can get how many issues there are in each status in a specific period and the “Difference” between the issues found on each date. With the specification that date columns are dynamics and I can select them by the page.
In the “Difference” column I want to obtain the difference between the issues found on each date; that is where the problem lies.
The “Difference” column works when the period is between the first day of a month and the last day of the same.
But the problem is when I want to choose a period between days of different months or a period smaller than a month. In this situation, the column “Difference” gives wrong values.
The formula used in this column is: ([Time].CurrentHierarchy.FirstSibling, [Measures].[Issues history]) - ([Time].CurrentHierarchy.LastSibling,
[Measures].[Issues history])
The goal would be for the “Difference” column to give correct values for any time period no matter which day is selected.
Next, I leave an image with the aforementioned situations:
Thank you very much in advance and I appreciate any answer
Sorry in the screenshot I forgot to show the variables that participate in the columns, rows, and pages.
Next, there is a screenshot of them:
Thanks again.
Hi everyone! I contacted support and they gave me the solution that worked for me:
The main problem is that the .FirstSibling and .LastSibling always return the first and the last days of the month in the default hierarchy of the Time dimension regardless of what is selected on the page filters.
I was able to verify that with the debugging function SetToStr(). More about this function here - SetToStr.
I used the VisibleColumnsSet() coupled with Min or Max functions and time conversion functions DateToTimeStamp and TimestampToDate to find the first and the last displayed dates.
The updated expression for “Difference” was as follows:
CASE WHEN
–comparing the current column date against last filtered date
–that allows displaying difference value for the last date only
–use hide empty columns to hide the blank columns
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
–min and max work on numeric values, therefore convert back to date
TimeStampToDate(
–finding the last date
Max(
–set of all visible columns
VisibleColumnsSet(),
–retrieve date of the each column in plain number format
DateToTimestamp([Time].CurrentHierarchyMember.StartDate)))
) = 0
THEN
–retrieve the last date and find value
Sum([Time].[Day].DateMember(
TimeStampToDate(
Max(
VisibleColumnsSet(),
DateToTimestamp([Time].CurrentHierarchyMember.StartDate)
)
)),
[Measures].[Issues history])
-
–retrieve the value for the first date
Sum([Time].[Day].DateMember(
TimeStampToDate(
Min(
VisibleColumnsSet(),
DateToTimestamp([Time].CurrentHierarchyMember.StartDate)
)
)),
[Measures].[Issues history])
END
Greetings to all and I hope this helps someone else!
credits to Oskars Laganovskis
eazyBI support
1 Like
