Assigning Expressions within the load script

[richard_pearce6]

Hi,

We have a requirement where a number of metrics defined within the load script have their own expression. For example:

Measure          ,Expression

Total               ,Sum(Sales)

Average          ,Avg(Sales)

We've created a chart where the Measure is the dimension and the expression is =only(Expression)

I've tried various combinations of dollar expansion and not been able to get it working.

Can someone provide assistance???

Thanks

Richard

[richard_pearce6]

Good thought Martyn (hope you're not calling me S?  )

Thinking about the table though both expressions would be true and wouldn't show / hide depending on the dimension and I'd have more than one column unless I'm missing something?

Also, I need to make it dynamic as the calulations may change or more could be added.

Rich             

[martynlloyd]

No way my Sage!

I was assuming that measure would be selected somewhere else, possibly as an 'always select one value' listbox

If that is not the case, then your chosen method is the way to go.

I use this technique to create an interactive report builder for users; we now have Sense (c) of course... lol.  

Regards,

Marty.

[#4]

This isn't pretty, but if you can get your measure and expression loaded in a different way, should be able to use the fields dynamically within your pick and match.

Just food for thought really

[richard_pearce6]

Yeh, I use a similar method with FirstSortedValue and a data island to do charts like that. Its a very handy technique.

Thanks for your input Martyn, muchly appreciated !

Rich

[jonathandienst]

Hi

No problem to do dynamically, just build a Pick(Match()) expression in the script:

Expression:

Load * Inline [

Measure    ,Expression

Total      ,Sum(Sales)

Average    ,Avg(Sales)

];

Concat:

LOAD

  chr(39) & Concat(Measure, ',') & chr(39) As cLines,

  Concat(Expression, ',') As cExpr

Resident Expression;

Let vLines = Replace(Peek('cLines'), ',', chr(39) & ',' & chr(39));

Let vExpr = Peek('cExpr');

Set vPick = Pick(Match(Measure, $(vLines)), $(vExpr));

DROP Table Concat;

Now in a table with dimension [Measure], use the expression: =$(vPick)

See attached.

HTH

Jonathan

[richard_pearce6]

Thanks for the example Jonathan. !!!

I'll have a look at this in the QV, cheers.

Rich