Using a selected value to sum rows within an expression

[#1]

Hi - I am very new to Qlikview. I have not been able to find a solution to my issue yet. I have Year and Month as dimensions, and deal amounts by month/year. On my chart, I would like to use the selected Year / Month as the 'as of'' month/year to sum the amounts. My expression is Sum (deal_amount). This works fine when I want the sum for a particular year and/or month. But that is not what I need. In SQL, this would translate to 'select sum(deal_amt), ... from ... where month >= '200901' .. group by ...' for example. How can I accomplish this? Thanks in advance for your help. -Najma

[#2]

Hi Najma,

You need to use 'Set Analysis'. I propose that you study it using the help file.

Your solution would be:

sum({1<month>={'200901'}>} deal_amount)

Please look at the help file to get a better understanding of how set analysis works.

Regards,

Werner

[#3]

Hi Najma,

see the attached example for help.

Good luck!

Rainer

[#4]

Hi.

Set analysis is new in 8.5 isn't it? I am on 8.2. Thats why I was wondering why the simplest expression was not working for me. In your example, Date.qvw, I couldn't see the numbers where set analysis was used in the expression. Thank you both for your help! And I need to upgrade.

Najma

[johnw]

Well, upgrading is probably a good idea, and makes this simpler, but it isn't a requirement to solve your problem.

Create an "As of Month" field that isn't connected to your main data. Then you can refer to that field in the expression. I'm not sure quite what you're after, but here's an example that would give you the sum of all deal amounts up through the selected month.

sum(if(Month<="As of Month",deal_amt))

One problem is that it is very slow on large data sets. If it doesn't perform adequately, you could handle it in the script by loading up a table that maps Months to the "As of Month" so that all months are mapped to the current as of month, all but the current month to the previous as of month, and so on. Then your expression becomes trivial again, sum(deal_amt).

Another problem is that actually selecting some Months will break it, because it will only compare the selected Months to the As of Month, which probably isn't what you want. You could always write a macro to clear your regular date selections when the "As of Month" is selected or changed, but that might be overkill.

[#6]

My 'As of Month' is actually the selected Year and Month from my displayed calendar. If I select 2008, January, then I want the sum for months >= 2008, Jan. How do I refer to the selected fields? And where can I get documentation on all this coding syntax, examples etc? Thanks! -Najma

[johnw]

What I'm saying is that you can't use the existing Month and Year as your "As of Month" and "As of Year", because they are directly connected to your data. When you select something from them, you are selecting exactly those dates. You can't solve this problem that way, because you don't want exactly those dates, you want other dates that depend on the selected dates. That can all be done using one calendar using set analysis in versions 8.5 and above, but that's not your situation. So I'm fairly confident that you need a NEW field or fields to solve the problem.

If you're wanting them to select a year AND a month, I suggest having a "Month/Year" field in your regular calendar (you don't have to display it) and an "As of Month/Year" field for your new field. I don't see a reason to make two selections when they can make one, but the solution would be similar for two.

As for syntax, that's all available in the help file, which includes a number of examples as well. The forum is also a good source for examples. Here's an example of using two disconnected date fields to perform fake "set analysis" of the type I think you're describing.

[#8]

Thanks. I've got it to work for MonthYear >= AsOfMonthYear.