Previous Month Comparison Issue

[#1]

Hey All,

I'm a noob to Qlikview and I know there is a lot of discussion and posts regarding the subject. I've educated myself a great deal on the matter; however, I cannot seem to figure this out.

I'm trying to use a set expression to show a given agents current month performance as well as their prior month performance within a straight table (see screenshot). I am having an issue with the PREVIOUS MONTH COLLECTIONS calculation. The formula i'm using is such:

sum({[Agent Performance Analysis]<Post_Month = {">=$(=monthstart(addmonths(today(),-1)))<=$(=addmonths(today(),-1))"},Post_Year=>} PRINPMT_28_1)

I've gone about calculating this formula several different ways and I cannot seem to get it to function correctly. I feel that I'm missing something.

Below, I should reflect the SUM of PRINPMT_28_1 for the month of January, 1, 2014 but I reflect a 0 value.

I have also attached an example qvw.

Many thanks,

[Oleg_Troyansky · Accepted solution]

Brett,

your particular problem is caused by the difference in field formats. Post_Month is formatted as a Month field (Jan, Feb, Mar, etc..) while the Set Analysis expression is using functions like MonthStart() and AddMonths(), that both return dates. So, the Set Analysis expression is trying to compare Jan to 1/1/2014, as an example, and the comparison can't quite work.

So, the "immediate" solution would be to transform the condition in such a way that would compare fields and values that are formatted identically.

However, I'd recommend to replace this complex set analysis condition with a simper one and to move the "heavy lifting" to the load script, by calculating two flags in your Calendar table (I noticed you don't have one, and I would recommend to create a Calendar table:

CM_Flag = 1 for all the dates that belong to "Current Month" (0 or null() for all other dates)

PM_Flag = 1 for all the dates that belong to "Prior Month" (0 or null for all other dates)

Once you created those two flags in your Calendar, the Set Analysis condition becomes extremely simple:

Current Month:     sum({[Agent Performance Analysis]<CM_Flag={1}>} PRINPMT_28_1)

Prior Month:          sum({[Agent Performance Analysis]<PM_Flag={1}>} PRINPMT_28_1)

As a side comment, the calculated dimension that you use in the chart, may become troublesome for performance, if your data should grow in volume. I'd recommend to create a corresponding field in the data structure and remove the calculated dimension.

Interestingly, this post is touching both topics that I will be teaching at the Masters Summit in Chicago, on April 1-3. One of my lectures is about advanced Set Analysis, including the issues with the Date formatting, and the other lecture talks about Performance Tuning, including a discussion about Calculated Dimensions. Come and learn both topics with us - Chicago, April 1-3 2014.

www.masterssummit.com

Cheers,

Oleg Troyansky

[#3]

Oleg,

Thank you very much for your time. I'm looking forward to the Masters Summit in April.

I'm in the midst of the calendar now,

Could you provide me an example of how this would like within the script? I'm at that point now with the calender.

"

CM_Flag = 1 for all the dates that belong to "Current Month" (0 or null() for all other dates)

PM_Flag = 1 for all the dates that belong to "Prior Month" (0 or null for all other dates)

"

Thanks,

[Oleg_Troyansky]

Brett,

there are probably many different ways, but here is one way of calculating it, assuming that the Date field is called PostDate:

load

PostDate,

...

IF( MonthStart(PostDate) = MonthStart (today()) , 1, null()) as CM_Flag,

IF( MonthStart(PostDate) = MonthStart (today(), -1) , 1, null()) as PM_Flag,

...

In this case, we are comparing the Month Start of the PostDate with the Month Start of today(). If they are the same, then the PostDate belongs to the Current Month. The same comparison, with an addition of the shift -1, gives us the Prior Month. You may need to tweak it a bit for your needs, but this is the general direction.

cheeers,

Oleg Troyansky

www.masterssummit.com

[Colin-Albert]

If you create flag fields containing 1 or null for the current month and prior month as suggested by Oleg, you can replace the set expression by a simple multiplication.

Current month expression.        Sum(PRINPMT_28_1 * CM_Flag)

Prior month expression.        Sum(PRINPMT_28_1 * PM_Flag)

[Oleg_Troyansky]

That's true, however using the Set Analysis expression is much better for performance, despite the quirky syntax.

This is too long of a discussion (we discuss it at length on my Performance Tuning session), but there is a number of good reasons for the Set Analysis expression to outperform the expression that uses multiplication by the flag.

[Colin-Albert]

That's an interesting point on performance, as I had understood that flag expressions using 1 and null, were faster than set analysis expressions.

[Oleg_Troyansky]

Colin,

I invite you to test it on a large data set and to publish your results. Also, join us at the Masters Summit where we talk about Performance Tuning and describe in detail why one solution performs better than the other.

Oleg Troyansky

www.masterssummit.com

[#9]

Oleg,

Thank you very much for your advice. I have made the recommended changes as a whole and honestly everything works as suggested. Thank you very much.

Internet High-Five my friend,

Colin, I appreciate your approach to the problem as well. It's nice seeing there's more than one way to "fry an egg". I appreciate both approaches for it allows me to see how others are constructing and approaching the problem and ultimately to Oleg's point the considerations of such for each.

Thanks again to both,

Happy Qlik-ing