Math within set analysis

[#1]

Hello,

I am having issues with using math within set analysis . More specifically, I am specifying a month in a list box and would like to calc , for example sake sum(sales), for the month specified and divide it by the same calc for twelve months earlier . This would make a year over year comparison of the calculation.

Currently, I am simply hard coding the month. I have selected Dec 09 in the list box and use the following expression.

Sum(Sales)/Sum({$<RollingMonth={"12/08"}>} Sales)

Is there a way to subtract twelve months within the set analysis , so I am able to select any month, and compare it to that month the year before?

Thanks for your help!

[#2]

Hello,

you can use formulas within set analysis, using the $ expansion mechanism.

sum ({$<RollingMonth={$(=AddMonth(Now,-12)}>} Sales)

I didn't test the formula, so there might be slight typos ... but you see the principle.

You might replace Now by another expansion with your selection.

hth,
Thilo

[stephencredmond]

Hi,

You are going to need to be careful here.

I am guessing that your "RollingMonth" field was probably created (like the training course) using the Date(date_field, 'MM/YY') format in the script. This created a Dual value (has both numeric [date] and text value).

This is all well and good if you start to use an expression like:

Sum({<RollingMonth = {"$(=Max(RollingMonth))"}>} Amount)

(if you want to stop hard coding)

Then you might think to use an AddMonths to compare your date 12 months ago like this:

Sum({<RollingMonth = {"$(=AddMonths(Max(RollingMonth),-12))"}>} Amount)

The problem is that the AddMonths function will leave you with only a date - not a Dual value. This set will not work!

To make it work, you need to make it a Dual again using the Date function:

Sum({<RollingMonth = {"$(=Date(AddMonths(Max(RollingMonth),-12),'MM/YY'))"}>} Amount)

Regards,

Stephen

[stephencredmond · Accepted solution]

This thread has inspired a blog post: http://qliktips.blogspot.com/2010/01/more-on-dual-problem-in-sets.html

Regards,

Stephen

[#5]

Hi Stephen,

For me, the following code is not working as you described:

Sum({<RollingMonth = {"$(=Max(RollingMonth))"}>} Amount)

If RollingMonth is a dual, the Max() function is returning an integer, and the set is apparently interpreting RollingMonth as a String. I've tried it with the double quotes (which is not documented in the QV help).

However, I could get the result I want by Using the function MaxString() in stead of Max():

Sum({<RollingMonth = {$(=MaxString(RollingMonth))}>} Amount)

The above code did bring the required result. I've tested it with both QV9R2 and QV9R4. I wouldn't be surprised if older versions had different behaviour. I'm very curious to know with which version you have tested it!

Kind regards.

Daniël