topic Re: SET ANALYSIS in QlikView

SET ANALYSIS

jduenyas — Wed, 14 Aug 2013 16:45:56 GMT

Hi all

In an example of set analysis which I have downloaded, an expression was written as such:

=if(sum({$<YR = {$(=max(YR - 1))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt)
> sum({$<YR = {$(=max(YR))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt),
sum({$<YR = {$(=max(YR))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt),
sum({$<YR = {$(=max(YR) - 1)}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt)
)

Although it is a bit convoluted I can descern that Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} is to find the Month which is "less than or equal Max Month of the Max Year".

My question though is regarding the double quote: When / where / why use double quotes vs single quotes if use any of them at all? And is there any written material regarding advanced construct and punctuation of expressions within set analysis?

Thanks

Re: SET ANALYSIS

Gysbert_Wassenaar — Wed, 14 Aug 2013 18:49:20 GMT

There used to be a difference between single and double quotes in set analysis expressions. As far as I know there isn't anymore. That's a good thing because it makes it possible to nest search strings.

The expression you posted can be simplified some using the rangemin function:

rangemin(

sum({$<YR = {$(=max(YR)-1)}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt) ,

sum({$<YR = {$(=max(YR))}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt)

)

or with some helper variables:

vCurrentYear: =max(YR)

vPrevYear: =max(YR)-1

vSumTranAmt: sum({$<YR = {$1}, MO = {"<=$(=max({<YR={$(=max(YR))}>} MO))"}>} TranAmt)

Expression: rangemin( $(vSumTranAmt($(vPrevYear))), $(vSumTranAmt($(vCurrentYear))) )

Re: SET ANALYSIS

jduenyas — Wed, 14 Aug 2013 18:56:58 GMT

Thank you Gysbert. You always come through shining!

One correction though:

Shouldn't vPrevYear = max(YR-1) actually be vPrevYear = max(YR) -1 ?

Thanks again.

Re: SET ANALYSIS

Gysbert_Wassenaar — Wed, 14 Aug 2013 19:09:25 GMT

Yeah, you're right. So much for blindly copying and pasting stuff people post at random sites . I've fixed the code now. Thanks.