Dynamic comparison of time periods

[#1]

Dear all,

I'm trying to set up an application that compares the selected time period with the preceding time period. What "preceding" is, should be defined by the user by way of a toggle select comparison box "Day, Week, Month, Quarter, Year".

Example:

User selects the dates for the current week. User wants to compare with the preceding week and sets the comparison box to "Week" Accordingly. What the chart/ set analysis should then do is having the "Actual" from the selected dates, and a comparison column with the same date values of the last week.

or

User selects a Monday and Thursday from the current week. User wants to compare this "actual" with the same Monday and Thursday from the preceding Month and thus sets the comparison box to "Month". Then the actual is shown for the current selection and the comparison column shows the monday and thursday of the preceding month.

so, the comparison box kind of shifts the entire calender for the comparions period by Day, Week, Month, Quarter or Year.

How to achieve that with set analysis? How to deal with leap years? how to deal with 31 and 30 days months?

- Min, Max expression on date is not working, because a user may want to pick any values and compare them to the preceding period.

- All what I found in the forum is YTD, MTD etc. and comparison of periods with min and max variable values...

hope you understand what I am trying to achieve. Looking forward to your suggestions.

Best regards, Felix

[Gysbert_Wassenaar]

I think I would create two alternate states and two sets of objects to let the user make selections in, one set for each alternate state. The user then has complete flexibility in choosing what to compare with what and how to deal with different month lengths and leap years.

The expressions in the charts will use the alternate states in the set modifier:

Actual: sum({ActualState} Amount)

Comparison period: sum({ComparisonState} Amount)

[sujeetsingh]

I too agree with Gysbert, it will be good to go with two alternate state which enable us to compare things based on two selections of same field or filter.

[#4]

I have the exact same issue but I have no idea how to set up two altered states. Can anyone provide some insight?

Thanks.

[Gysbert_Wassenaar]

You can create new alternate states on the General tab of the Document Properties window. After creating the alternates states you can assign them to objects on the General tab of the objects properties window. See the video in this blog post: A Closer Look at the Comparative Analysis

[#6]

Thank you so much, Gysbert. This is great!

[maksim_senin]

Hi Felix,

Independently of a method you select you will deal with set analisys, so it's worth to read about it if you ain't sure of your knowledge of this (I cannot say I'm a guru in this even for 50% so personally I read manuals periodically).

Alternate states is very good approach. Please consider this.

Also time periods (aka like-for-like analysis) can be compared by use of variables which define the periods in set analysis, e.g. =SUM({$<$(vCurrPeriod)>}  Amount), where vCurrPeriod may contain Date={">=$(vCurrentPeriodStart)<=$(vCurrentPeriodEnd)"} and in turn vCurrentPeriodStart and vCurrentPeriodEnd contain first and last dates of current period.

First approach it easier to implement at formulas level, as soon as second approach being more complex in development allows to implement more user friendly applocation where user can select (e.g. via button) "Current month to previous year" or "Current month to previous month" instead of selection of necessary periods in different alternate states.

I hope this helps.

Best regards,

Maxim

[Anonymous]

Realize this is a long time since you posted, but I disagree with the others and wanted to post it for debate or consideration. I think Alternate States are overkill for what you're trying to do. If it were me, I would use an inline table to coordinate different sets of expressions against each other, and use a dynamic lookup based on the user's selection of year, month, quarter, etc.

Something like:

SET currentYearExpression = sum({<...current year set analysis...>} Sales);

SET priorYearExpression = sum({<...prior year set analysis...>} Sales);

Load * Inline [

Period, CurrentExpression, PriorExpression

Year, $(currentYearExpression), $(priorYearExpression)

Quarter, $(currentQuarterExpression), $(priorYearExpression)

Month, $(currentMonthExpression), $(priorMonthExpression)

];

Then in the expression in your chart to show the comparison you do something like:

=$(=CurrentExpression)/$(=PriorExpression)

And you set "Always One Selected Value" on the Period ListBox.

That will dynamically put the correct expressions either side of the comparison formula and give you what you wanted.

The drawback of this approach would be that it locks in which periods can be compared to each other, but in my experience that fits most user's needs.

Good luck!

[Krishnan92]

Hello! I have a similar requirement, but this not its not the chart but in a single straight table. Id will be common field for both sets of data but my first three fields and next three fields are actually dynamic .i.e. my first three fields lets call 'Version 1' and my second three fields called 'Version 2'. Hence two filters and users can select 'Actual 2021' under version 1 filter and 'Actual 2022' in version 2 filter and the fields should populate the value accordingly. Could you please help me out ?

[joy_paje]

I also have this issue, did you solve this?