topic Re: Dollar Syntax in QlikView

Dollar Syntax

Tue, 13 May 2014 04:08:40 GMT

Morning,

I was wondering if anyone has an "Idiots guide to dollar syntax"?, further more if anyone has a similar "idiots guide to Set Analysis"?

Thanks

Re: Dollar Syntax

anbu1984 — Tue, 13 May 2014 05:16:31 GMT

Set Analysis

Re: Dollar Syntax

ashfaq_haseeb — Tue, 13 May 2014 05:34:44 GMT

Hi,

Below post might help you.

Romancing with Set Analysis

Even I have some basic attachment that might help you.

Regards

ASHFAQ

Re: Dollar Syntax

Tue, 13 May 2014 05:55:18 GMT

Thanks Ashfaq,

I have read a similar document, my problem now is how to incorporate a variable into this, say for example in the following expression:

Sum({$<Year={2008}>} Sales)

I want to replace 2008 with the max year in the set, for which i have already created a variable called vMxYear. Qlikview rejects the following:

Sum({$<Year={vMxYear}>} Sales) and also

Sum({$<Year={$(vMxYear)}>} Sales)

Cheers

Re: Dollar Syntax

ashfaq_haseeb — Tue, 13 May 2014 06:05:54 GMT

Hi may be like this.

Sum({$<Year={'$(=vMxYear)'}>} Sales)

Regards

ASHFAQ

Re: Dollar Syntax

Tue, 13 May 2014 06:08:55 GMT

will give it a try...thanks

Re: Dollar Syntax

Tue, 13 May 2014 06:14:31 GMT

Ok that works, but it only works when the particular year is selected, how do i change it so that regardless of what is selected, that calculation returns only the last Year.

Thanks for the help

Re: Dollar Syntax

jonathandienst — Tue, 13 May 2014 06:19:35 GMT

Stephen

I assume that vMxYear is a variable. Define the variable

=Max(Year)

(the = sign is included in the definition, so this variable always returns the maximum possible year. The max possible year is the maximum in the data set if nothing is selected, or the maximum of the selected values if one or more year is selected.

Then use it as described previously.

HTH

Jonathan

Re: Dollar Syntax

Tue, 13 May 2014 06:26:42 GMT

Thanks but not working, if nothing is selected and everything is exactly as you guys told me to do, it sums everything.

Re: Dollar Syntax

anbu1984 — Tue, 13 May 2014 06:34:51 GMT

Can you show your app?

Re: Dollar Syntax

jonathandienst — Tue, 13 May 2014 06:45:51 GMT

Stephen

There are two possible causes:

Year is not properly linked to the fact table in your model, so selecting year does not change the totals.
vMaxYear is not working properly.

To check the first, verify the that the Sums or Counts change when you make a selection of Year. If necessary, add a Year list box.

To check the second, create a new chart/table, enter

Sum({$<Year={'$(=vMxYear)'}>} Sales)

as the expression. Do not enter an expression label. Now the table caption should display the expression without the $ expansion and the column label should display the expression with the dollar expansion. That should give you a clue as to what's happening.

HTH

Jonathan

Re: Dollar Syntax

Tue, 13 May 2014 08:05:45 GMT

Thanks All,

Got it working, would help in future if i actually remembered that my Fields are not named the same 🙂