topic Re: Set Analysis in QlikView

Set Analysis

carolin01 — Tue, 20 May 2014 14:34:16 GMT

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...

Many thanks for your comments.

Best regards

Carolin

Re: Set Analysis

Tue, 20 May 2014 14:36:52 GMT

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

Re: Set Analysis

Tue, 20 May 2014 14:37:56 GMT

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

Quotations around Revenue?

Re: Set Analysis

carolin01 — Tue, 20 May 2014 14:50:01 GMT

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

carolin01 — Tue, 20 May 2014 14:51:06 GMT

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

Re: Set Analysis

Tue, 20 May 2014 14:52:03 GMT

Try:

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

Re: Set Analysis

carolin01 — Tue, 20 May 2014 14:55:38 GMT

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

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

Re: Set Analysis

Tue, 20 May 2014 14:57:10 GMT

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

Just checking its not the current selections

Re: Set Analysis

manideep78 — Tue, 20 May 2014 14:59:29 GMT

Try this,

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

Re: Set Analysis

Tue, 20 May 2014 14:59:55 GMT

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

stigchel — Tue, 20 May 2014 15:03:30 GMT

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

{<Month='$(=Date(AddMonths(Date#(Max([$(=Dimension)]),'YYYYM'),-1),'MMM-YY'))'>}

Re: Set Analysis

carolin01 — Tue, 20 May 2014 15:35:38 GMT

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

Tue, 20 May 2014 15:37:05 GMT

Is your FYear a number? try MaxString(FYear)

Re: Set Analysis

carolin01 — Tue, 20 May 2014 15:50:38 GMT

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

Re: Set Analysis

maxgro — Tue, 20 May 2014 16:27:08 GMT

does it works?

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

and what's the result of a textbox with?

=Max(FYear)

Re: Set Analysis

rwunderlich — Tue, 20 May 2014 16:32:04 GMT

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

carolin01 — Wed, 21 May 2014 08:17:13 GMT

=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

carolin01 — Wed, 21 May 2014 08:26:37 GMT

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

maxgro — Wed, 21 May 2014 08:34:08 GMT

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

- read Rob suggestion

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

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

or post your doc

Re: Set Analysis

preminqlik — Wed, 21 May 2014 08:36:54 GMT

TRY THIS

=vRevenueTotalMax

=$(vRevenueTotalMax)