topic Re: Importing and expanding $variables in QlikView

Importing and expanding $variables

adamdavi3s — Tue, 03 Jan 2017 12:31:34 GMT

I am having a mind blank....

I want to store some calculations in excel/DB and then import them, basically to create a custom report format in a pivot table

All of the expressions are already in variables.

The upload spreadsheet for example is:

_ReportLevel	_ReportHeading	_ReportCalculation	_ReportFormat
s	Weekly Profitability
	Issued	$(v_exp_jobinvoicedcount)	#,##0
	Value	$(v_exp_jobinvoicedvalue)	£#,##0;-£#,##0
	Avg Value	$(v_exp_jobinvoicedvalue) / $(v_exp_jobinvoicedcount)	£#,##0;-£#,##0
a	Week Average		£#,##0;-£#,##0

What syntax do I then need to use to calculate the relevant variable if _ReportHeading is the dimension?

marcus_sommer — Tue, 03 Jan 2017 13:39:20 GMT

It's not quite clear for me what do you want exactly to do - but I think the following will be helpful for you: Re: Evaluate expression based on field value.

- Marcus

adamdavi3s — Tue, 03 Jan 2017 13:48:59 GMT

Hi Marcus,

I think that a pick match might be the only way in this case unfortunately, however I have just whipped up a basic example of what I hope to achieve.

In the attached example the pick match does work but it would be nice to make it more dynamic

=pick(match(_Header,'average','expression1','expression2'),$(v_expression1) / $(v_expression2),$(v_expression1),$(v_expression2))

Adam

adamdavi3s — Tue, 03 Jan 2017 13:56:03 GMT

Hmmm I wonder if I could dynamically build the pick match instead, at least that would offer some sort of automation

adamdavi3s — Tue, 03 Jan 2017 14:10:29 GMT

Close, but now I can't figure out the syntax to use the pick/match variable!

adamdavi3s — Tue, 03 Jan 2017 14:14:51 GMT

got it

=$(=$(v_pick_match))