SET Analysis | Sum only to month in dimension

[schmidtj]

Hi,

i have a problem with a measure where i need a sum of hours of the months until the month on the x-axis.
This is my example data to show my problem:

// This data shows the used hours per work package per month
DATA1:
LOAD * INLINE [
WorkPackage, MonthOfHour, Hours
...
];

// This data shows the status the work packages had in the recent months
DATA2:
LOAD * INLINE [
WorkPackage, MonthOfHistory, Status
...
];

My goal is the following:
I need to do a line chart where i show how much hours went to the work packages with status = x in the past.
That means ...
- on the xAxis i need to put the MonthOfHistory
- as a measure i need the sum of hours for the work-packages until MonthOfHistory

So my measure formular would look like this, however i dont know how to define that only the hours up to MonthOfHistory will be summed up.

Sum
(
    {
        $<
            Status = {'x'},
            MonthOfHour = {"<=MonthOfHistory"}
        >
    }
    Hours
)

Can anybody help me with this?

[MayilVahanan]

Hi @schmidtj 

MonthOfHistory & MonthOfHour are in date format or month name like Jan, feb, etc?

MonthOfHistory  <- is upto last month?

MonthOfHour <-- is contains all months upto current month?

[schmidtj]

Hi!

- yes, MonthOfHistory & MonthOfHour are in date format
- MonthOfHistory has data for every month in the past until actual month
- yes, MonthOfHour contains all months upto current month

Thanks!

[MayilVahanan]

Hi

In that case, try like below

Sum({< Status = {'x'},  MonthOfHour = {"<=$(=Date(Max(MonthOfHistory))"} > }Hours)

or,

Join/Concatenate the both tables based on  combination of workplace & monthofhistory and Workplace&MonthOfHour.  And create the  Mastercalendar based on the date field and use it in the line chart

Ex;

Dim: MonthName 

Exp: Sum({< Status = {'x'}> }Hours)

[schmidtj]

Hello!

I tried it like that before but it is not working i'm sorry.
The Max(MonthOfHistory) will get you the max Date on the overall data but not in relation to the date on the x-axis.
So the formular on the right in SET ANALYSIS doesnt really care about the chart unfortunately 😕

[MayilVahanan]

Can you send the sample with expected o/p?