topic Math within set analysis in QlikView

Math within set analysis

Mon, 25 Jan 2010 19:09:35 GMT

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!

Math within set analysis

Mon, 25 Jan 2010 19:32:28 GMT

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

Math within set analysis

stephencredmond — Mon, 25 Jan 2010 19:39:14 GMT

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

Math within set analysis

stephencredmond — Mon, 25 Jan 2010 20:00:21 GMT

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

Regards,

Stephen

Math within set analysis

Sun, 16 May 2010 15:17:00 GMT

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