topic SET Analysis | Sum only to month in dimension in App Development

SET Analysis | Sum only to month in dimension

schmidtj — Thu, 12 Aug 2021 15:09:34 GMT

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?

Re: SET Analysis | Sum only to month in dimension

MayilVahanan — Fri, 13 Aug 2021 04:48:45 GMT

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?

Re: SET Analysis | Sum only to month in dimension

schmidtj — Fri, 13 Aug 2021 06:05:32 GMT

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!

Re: SET Analysis | Sum only to month in dimension

MayilVahanan — Fri, 13 Aug 2021 07:28:10 GMT

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)

Re: SET Analysis | Sum only to month in dimension

schmidtj — Mon, 16 Aug 2021 10:55:02 GMT

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 😕

Re: SET Analysis | Sum only to month in dimension

MayilVahanan — Tue, 17 Aug 2021 02:36:56 GMT

Can you send the sample with expected o/p?