Qlik Community

QlikView Security & Governance

Discussion Board for collaboration on QlikView Security and Governance.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
tabletuner
Contributor III

Last year comparison

Dear Reader,

I want to compare YTD sales of this year with YTD sales of last year.

Suppose I have a straight table with products A and B in the product dimension.

The selected period is 2014.

Expression 1 is sum(Sales)

Expression 2 is sum({$<Date={$(='">='&yearstart(max(Date),-1)&'<='&addyears(max(Date),-1)&'"')},Year>} Sales)

The table displays the data well. However, when i select 1 value in the product dimension of this chart, the set analysis will be limited to the associated dates for this product's sales selection. In the example the sales of last year will decline from 200 to 0 after selecting product A. I know it is associative but an enduser will never accept this!

I used to think that these kinds of set analysis expressions are best practices for last year comparisons. However i have my doubts now. A possible solution would be a calendar data island and include the selection in that calendar into the set analysis of each expression i have.

Another solution would be to ignore the selection by extending the set analysis with all possible dimensions i could possibly select in my app.

Both solutions offer a lot of hassle to maintain. So i was wondering if anyone knows a good solution for this problem?

Regards,

Tjeerd

8 Replies
whiteline
Honored Contributor II

Re: Last year comparison

Hi.

The best approach depends on the requirements

You can add month # into calendar and use it for YTD period selection.

The problem with your solution is that actually there is no time selection except year and you try to calculate it dynamically.

giakoum
Honored Contributor II

Re: Last year comparison

I am attaching a sample app that can be helpfull, however I would suggest that you create flags in your reload script to handle this. See Calculating rolling n-period totals, averages or other aggregations

tabletuner
Contributor III

Re: Last year comparison

Hi Ioannis and Whiteline,

I still experience the same problem. I can illustrate it better by using the qvw that Ioannis attached.

Please go to the tab 'Set Analysis - Dynamic Advanced'.

Select Salesperson 'Callins, Joan'

Select Year '2007'

Select Months Jan-Feb-Mar-Apr

Please in the table in column (2006 (YTD)' the value 1.589 for product 'Chantell Shirt'

Now select the product 'Chantell Shirt'.

The value is decreased to 463.

I think it is hard to explain the user why this value has decreased because the user didnt directly limit the period selection. He did it indirectly.

How do you guys explain this? Or do you use a workaround?

Regards,

Tjeerd


mov
Esteemed Contributor III

Re: Last year comparison

My preference, in situations like this, is to use function InYearToDate().  Here it would be:

YTD       sum(if(InYearToDate(Date, today(), 0), Sales))

LYTD     sum(if(InYearToDate(Date, today(), -1), Sales))

If you want to make it independent from some (or all) selections, just use set analysis.

Similar can be used for MTD and QTD.

Additional advantage of this method is that today() can be replaced with any date.  Typically I use a variable here.

Regards,

Michael

tabletuner
Contributor III

Re: Last year comparison


Hi Michael,

Thanks for the answer.

In my case year and month(s) selections are required. So the inyeartodate() function wont be useful because it cant analyse the excluded data.

'just use set analysis' is the core of the problem i guess. I thought i explained this in my last post. Maybe you missed something? Is my question unclear?

Regards,

Tjeerd

mov
Esteemed Contributor III

Re: Last year comparison

Yes, I've missed something - I've answered your opening question rather than the follow-up.

From the follow up, this specific problem can be resolved this way, see addition in bold:

sum({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))},ProductName=>} Month))"}>} LineSalesAmount)

Hope it helps you to resolve other similar issues...

tabletuner
Contributor III

Re: Last year comparison


Hi Michael,

Thanks for the quick reply.

For this particular example (chart and selection) it will work. However, in my opening post i suggested the same solution but concluded that it is a hassle to maintain.

This because in theory any dimension selected in the data cloud could cause the same problem.

mov
Esteemed Contributor III

Re: Last year comparison

Well, I still recommend using InYearToDate().  Requirement to select Year and Month can be replaced with setting the base date variable which can be in any year and month.  It is simple and comprehensive way.  Use {1} if you want to ignore all selections.

The method you prefer is OK. To minimize the hassle, use the same {1} to ignore all selections except explicitly specified.

Community Browser