Month by Month Comparison

[#1]

Hi,

I need to show a month by month comparison for the latest month only in Pivot table. My report has expression values for all the months of a year(YYYYMM format) but need to compare current running month with last month expression value.

Please help on it.

[#2 · Accepted solution]

You can accomplish this through Set Analysis and a dollar sign expansion.

Your expressions should look something like this:

Sum( {<Month = {$(#=Month(Now()))}>} Fact_Field)

The portion in parenthesis after the dollar sign is the dollar sign expansion and will be evaluated before the Set Analysis is considered. You would do something similar for the previous month.

[Oleg_Troyansky]

If you are using 8.5, you can use Set Analysis to limit data to Current Month and to Prior Month.

Alternatively, you can pre-load Flags in your data - for the Current Month, set CurrMonth_Flag = 1 (keep it as null() for all other months) and for the Previous Month, set PriorMonth_Flag = 1. Then, your chart expressions are very simple:

Curr Month Sales = sum(Sales*CurrMonth_Flag)

Prior Month Sales = sum(Sales*PriorMonth_Flag)

cheers,

Oleg

[#4]

Hi,

I amnew to QlikView so not aware on Set Analysis. Can you please provide how I can calculate for current Month and Last month using set analysis.

[#5]

The expression I wrote above is Set Analysis. For an overview of Set Analysis, see page 327 of Book III of the Reference Manual. For forum entries related to Set Analysis, click the Set Analysis link under Popular Tags on the right of the forum topics.

There are quite a few variations of Set Analysis that would accomplish what you're looking for. Oleg's method of using flags is just as simple and effective. Since only you know how your data looks, we can't write the expressions for you.

[johnw]

Even with flags, I'd probably use set analysis. I believe that sum(Sales*CurrMonth_Flag) will force QlikView to examine every row. With set analysis, sum({<CurrMonth_Flag={1}>}Sales), I'm pretty certain that QlikView will only examine the rows with the flag set.

While I don't guarantee that my explanation is the right one, testing appears to show the expected performance differences. I created a 10 million row table with three customers assigned at random, a flag assigned at random, and a sales amount assigned at random. I made three different charts for the sum of flagged sales by customer, with different expressions to return the same result. Here is the calculation time for each:

531 sum({<Flag={1}>Sales)
750 sum(Flag*Sales)
781 sum(if(Flag=1,Sales))

(edit: That was with the flag set on 50% of the records. With the flag set on only 5% of the records, I see this:

141 sum({<Flag={1}>Sales)
687 sum(Flag * Sales)
641 sum(if(Flag=1,Sales))

And with only 0.5% of the records flagged:

78 sum({<Flag={1}>Sales)
656 sum(Flag * Sales)
594 sum(if(Flag=1,Sales))

Basically, the larger the table, and the smaller the percentage of rows you're trying to identify, the more it will help to use set analysis over an if or a multiply. I personally just make it a habit to use it all the time.)

[#7]

Thanks for the explanation John. I like to use Set Analysis because it is so easy to implement. I almost take the calculation effeciency for granted. I've never seen it quantified as actual times before. I am suprised that an if seems to be less costly than (or at least equally costly to) a multiplication though.

The dataset I am currently working with was designed prior to Set Analysis being introduced, so it utilizes flags. Using flags along with Set Analysis seems to offer the best of both worlds, giving you speed and an easy to follow syntax.