Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic comparison of time periods

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

7 Replies
MVP & Luminary
MVP & Luminary

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)


talk is cheap, supply exceeds demand
Master III
Master III

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.

Not applicable

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

Thanks.

MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Not applicable

Thank you so much, Gysbert. This is great!

Partner
Partner

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

Contributor II
Contributor II

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!