topic Re: Dollar-Sign Expansion with Parameters - does not like commas in QlikView

Dollar-Sign Expansion with Parameters - does not like commas

RedSky001 — Wed, 09 Nov 2011 13:26:40 GMT

Same problem has identified here http://community.qlik.com/message/118608#118608

I want to use Qlikview parameters (See "Dollar-Sign Expansion with Parameters" in the reference manual)

to help me format numbers conditionally using a function.

My data set has numbers betwen 1 and 10,000,000

Anything above 1,000 I'll divide by 1,000 and suffix with K

Anything above 1,000,000 I'll divide by 1,000,000 and suffix with Mil

vNumberFormatter:

if($1 > 1000000,num(($1 / 1000000),'#,##0 Mil')

,if($1 > 1000,num(($1 / 1000),'#,##0 K'),num($1,'#,##0')))

Therefore:

=$(vNumberFormatter($(vMySetAnylsisVariable))

vMySetAnylsisVariable contains a set anylsis statement and because I'm using more than one set modifier, I have to use commas, these are being treated as additional paramters by Qlivkiew.

Therefore with the following statement afterthing after the first comman is evalualted as a second paramter, not what I want!

sum({$<Date={">=$(vMinDate)<=$(=max(Total Date))"}, [Ad Local End Date Floor]-={'<$(=min(Total Date))'},[Ad Local Start Date Floor]-={'>$(=max(Total Date))'}>} [Impressions (Booked) Mgmt])

Has anyone got a workaround for this, other than having to write the same expression in every variable containing a number...

Mark

Dollar-Sign Expansion with Parameters - does not like commas

swuehl — Wed, 09 Nov 2011 13:54:36 GMT

Not 100% sure, but I think you shouldn't have a problem if the set expression variable would be evaluated to value just before passing it to the dollar sign expansion as parameter.

I got something working like this after playing around with adding equal sign before the variable definition.

Could you upload a small example file? I think we could make this work.

Regards,

Stefan

Dollar-Sign Expansion with Parameters - does not like commas

RedSky001 — Wed, 09 Nov 2011 14:14:31 GMT

Hi Stefan,

I've just attached a sample qvw. See "chart 1", it's currently not displaying anything...

Mark

Dollar-Sign Expansion with Parameters - does not like commas

swuehl — Wed, 09 Nov 2011 14:34:17 GMT

Just add an equal sign before your set expression variable definition in variable overview, like

=sum({$<Date={">=$(vMinDate)<=$(=max(Date))"},[Ad End Date]-={"<=$(=min(Date))"}>}Imp)

I now get the chart working, not sure if the formatting is correct, though (but it looks like).

Hope this helps,

Stefan

Re: Dollar-Sign Expansion with Parameters - does not like commas

RedSky001 — Wed, 09 Nov 2011 14:42:17 GMT

Stefan,

That's done the trick but now highlighted another problem.

The sum expression is not being evaludated over the dimensions, each row has the same figure, almost as if I had sum(total... but I don't! Any ideas why that's happening? So close!

Just read the following article.

http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

By putting an equals sign in the variable Qlikview evaluiates it immidatly at the document level, ignoring my dimensions, therefore this is not a solution to my problem.

Mark

Message was edited by: msheraton

Dollar-Sign Expansion with Parameters - does not like commas

swuehl — Wed, 09 Nov 2011 15:52:25 GMT

Yes, I see.

Evaluating the set expression before feeding it into the variable function was my intention, but I haven't thought about the implications on evaluating per dimension. So it seems that we are back to the beginning.

Re: Dollar-Sign Expansion with Parameters - does not like commas

RedSky001 — Wed, 09 Nov 2011 15:56:08 GMT

Well it thought me something I didn't know so I appreciate that. If you have any futher ideas please let me know!

Re: Dollar-Sign Expansion with Parameters - does not like commas

Wed, 01 Aug 2012 18:51:10 GMT

A set analysis expression is only evaluated once per chart. After it is evaluated and defines a data set, that data set is then carved up according to the chart dimensions. Therefore, it is not possible to use dimensions in set analysis because the set is evaluated and applied to the data model before the dimensions of the chart.

The following document shows one way of 'getting around' this limitation, not sure if it will work for you:

http://community.qlik.com/docs/DOC-1335

Re: Dollar-Sign Expansion with Parameters - does not like commas

Thu, 02 Aug 2012 00:37:22 GMT

The problem is that the variable with the set analysis is expanded within the parenthesis and before it is passed to the formatter. You can change the values to pass the variable name and expand the variable in the formmater. Try setting vNumberFommatter to

if($($1) > 1000000

or $($1) < -1000000

,num(($($1) / 1000000),'#,##0 Mil')

,if($($1) > 1000

or $($1) < -1000

,num(($($1) / 1000),'#,##0 K')

,num($($1),'#,##0')

))

and using =$(vNumberFormatter(vMySetAnylsisVariable)) as your expression. That should do the trick.

EDIT: I didn't notice that this was an old post but I hope it helps somebody.