20 Replies Latest reply: May 22, 2014 11:29 AM by Carolin Borchert

# Set Analysis

Hi,

Could anybody help me to correct my variable:

vRevenueTotalMax = sum({\$<FYear = {\$(=Max(FYear))}, OrderPhase_Desc = {Revenue}>} NettAmount\$(vCurrency));

I would like to see the Revenue for the Maximum Year that I choose from a List box. But it´s not calculating anything.

It´s for sure something about brackets etc or similar...

Best regards

Carolin

• ###### Re: Set Analysis

Is NettAmount\$(vCurrency) supposed to read as NetAmount\$(vCurrency)?

• ###### Re: Set Analysis

NetAmount\$(vCurrency) should be read as e.g. NetAmountEUR or NetAmountUSD

• ###### Re: Set Analysis

sum({\$<FYear = {\$(=Max(FYear))}, OrderPhase_Desc = {'Revenue'}>} NettAmount\$(vCurrency));

Quotations around Revenue?

• ###### Re: Set Analysis

Quotation didn´t seem to help. This is a formula that I use which is working:

= sum({\$<OrderPhase_Desc = {Revenue}>} NettAmount\$(vCurrency));

Now it should include the Max-Year Term...

• ###### Re: Set Analysis

Try:

vRevenueTotalMax = sum({\$<FYear = {Max(FYear)}, OrderPhase_Desc = {Revenue}>} NettAmount\$(vCurrency))

• ###### Re: Set Analysis

Unfortunately not: this is how it´s looking like:

sum({\$<FYear = {Max(FYear)}, OrderPhase_Desc = {Revenue}>} NettAmountUSD)

• ###### Re: Set Analysis

sum({\$<FYear = {Max({\$}FYear)}, OrderPhase_Desc = {Revenue}>} NettAmountUSD) ?

I feel like FYear = {Max(FYear)} does not consider the current selection, so maybe the above will work.

• ###### Re: Set Analysis

sum({<FYear={\$(=Max(FYear))},OrderPhase_Desc={'Revenue'}>}NettAmount\$(vCurrency))

Just checking its not the current selections

• ###### Re: Set Analysis

Try this,

vRevenueTotalMax = sum({\$<FYear = {"\$(=Max(FYear))"}, OrderPhase_Desc = {'Revenue'}>} NettAmount&\$(vCurrency));

• ###### Re: Set Analysis

Unfortunately not - I maybe this information helps:

This is working:

= sum({\$<OrderPhase_Desc = {Revenue}>}    NettAmount\$(vCurrency))

This is not working:

= sum({\$<FYear = {"\$(=Max(FYear))"}>}  NettAmount\$(vCurrency))

• ###### Re: Set Analysis

Depending on format of your year (date?) consider the following:

use the date function to give the set expression the string it is expecting using the dual date function

• ###### Re: Set Analysis

Is your FYear a number? try MaxString(FYear)

• ###### Re: Set Analysis

Good question - I didn´t know it and tried but unfortunately it still didn´t work...

• ###### Re: Set Analysis

does it works?

= sum({\$<FYear = {2014}>}  NettAmount\$(vCurrency))

and what's the result of a textbox with?

=Max(FYear)

• ###### Re: Set Analysis

=Max ( FYear) gives me the maximum year from the selection that I do in a Listbox, which is correct.

This is working, too:

=sum({\$<FYear = {2014}, OrderPhase_Desc = {\$(vOrderPhaseDesc)}>}NettAmount\$(vCurrency))

• ###### Re: Set Analysis

- replace in working expression 2014 with "\$(=Max(FYear))"

- what's the expression expanded if you select year 2014, 2013, .....?

do you get 2014 for "\$(=Max(FYear))" or what?

• ###### Re: Set Analysis

I think we are getting close now. In a textbox this is working and gives a correct result:

=sum({\$<FYear = {\$(=Max(FYear))}, OrderPhase_Desc = {'Revenue'}>} NettAmount\$(vCurrency))

Now I used this to set a variable:

set vRevenueTotalMax = sum({\$<FYear = {\$(=Max(FYear))}, OrderPhase_Desc = {'Revenue'}>} NettAmount\$(vCurrency));

Then I used the variable in a text box but the result is Null:

=\$(=vRevenueTotalMax)

• ###### Re: Set Analysis

Remove the label from your expression. Then hover over the column heading and you'll be able to see exactly how the expression is being expanded. That may provide a clue.

-Rob

• ###### Re: Set Analysis

Hello together,

Many thanks for all of your help. All answers were helpful but I contacted our consultant in the meantime and got a solution. As you might be interested I post it here:

Possibility 1:

vRevenueTotalMax = 'sum({\$<FYear ={' & chr(36) & '(=Max(FYear))}, OrderPhase_Desc = {''Revenue''}>} NettAmount\$(vCurrency))';

Possibility 2:

let vRevenueTotalMax = 'sum({\$<FYear ={' & chr(36) & '(=Max(FYear))}, OrderPhase_Desc = {' & chr(39) & 'Revenue' & chr(39) & '}>} NettAmount\$(vCurrency))';

The reason seemed to be that for some reason inspite of the set statement Qlik View calculated the formula directly when running the script. This always caused an "internal error". So the solution was to use a let statement instead and link the part with "&" as you can see from above. Now I´m able to maintain these formulas in the script and use it for several charts.

Many thanks once more for all of your kind help.

Best regards,

Carolin