topic Re: Can i use dimension as expression in QlikView

Can i use dimension as expression

rahulhv1 — Wed, 25 Nov 2020 16:16:04 GMT

Can we user dimension as expression . Below is the example.

I have an excel sheet as under

Name	Formula
rahul	=SUM(2+1)
vilas	=SUM(4+1)
santosh	=SUM(5+1)

I have pulled this in qlikview document. Now i have created a table chart and i want to use Fromula column as an expression so that the out put would be as under.

Name	Formula
rahul	3
vilas	5
santosh	6

Re: Can i use dimension as expression

sergio0592 — Sat, 28 Oct 2017 10:31:31 GMT

Hi, try with :

LOAD

Name,

Evaluate(Formula) as Output_formula

FROM Data.xlsx

(ooxml,embedded labels, table is Sheet1);

Re: Can i use dimension as expression

rahulhv1 — Sat, 28 Oct 2017 10:36:49 GMT

Thanks Jean for quick reply , but sorry to say, it isn't working

Re: Can i use dimension as expression

rahulhv1 — Sat, 28 Oct 2017 10:41:16 GMT

Sorry, its working. But in the Formula column i wish to use Set analysis expression and that i want to use.

Re: Can i use dimension as expression

Clever_Anjos — Sat, 28 Oct 2017 10:54:35 GMT

You can´t reuse an expression of one object into another, but you can create a variable with your expression and use this variable into any charts needed

Re: Can i use dimension as expression

rahulhv1 — Sat, 28 Oct 2017 11:44:05 GMT

Thanks for the reply. I can create the variables, but there are almost 150 expressions which i need to create to form a single table and creating these many variables is not suitable. I am creating a dash board which has many more KPI's like : Sales, Operations (OEE, OPE, IHR, COPQ), Raw material consumption, Variable costs, fixed costs, HR Related data and so on. In addition to the actual values, i am having budgets of the same as well. Keeping this in mind , i wanted to write expressions in Excel file beside the KPI name , and use the same.

Re: Can i use dimension as expression

Clever_Anjos — Sat, 28 Oct 2017 11:57:24 GMT

It´s in portuguese, but the example can help you Qlikview utilizando Expressão com origem no Excel

Re: Can i use dimension as expression

MarcoWedel — Sat, 28 Oct 2017 21:56:35 GMT

please post a sample application including some data and expressions to test with.

thanks

regards

Marco

Re: Can i use dimension as expression

vinieme12 — Sun, 29 Oct 2017 09:39:31 GMT

use the Only() function

Loading Expressions From File - WITHOUT creating variables in Script

Re: Can i use dimension as expression

rahulhv1 — Mon, 30 Oct 2017 03:50:24 GMT

Thank you so much. Can i have the same in English, it will help me in understanding the logic.

Re: Can i use dimension as expression

rahulhv1 — Mon, 30 Oct 2017 04:19:51 GMT

Hi,

Below is the KPI table , This is an INPUT Excel file (There are more than 50 KPIs, but here only giving few for simplicity)

SrNo	KPI	Pillar	Unit	Formula
P1	Sales	Mktg	Rs Lacs	*=num(sum({<FiscalYear={$(=max(FiscalYear))},Date1 ={">=$(=monthstart(max({<Flag={'Actual'}>}Date1)))<=$(=max({<Flag={'Actual'}>}Date1))"},DimName={'Total Net Sales'}>}[Amount In LC]-1)/100000,'###0')**
P2	Overall Plant Effectiveness (OPE)	KK	%	=((sum({<FiscalYear={$(=max(FiscalYear))},Date1 ={">=$(=monthstart(max({<Flag={'Actual'}>}Date1)))<=$(=max({<Flag={'Actual'}>}Date1))"}>}PtimeFinal)
P3	Plant effective Utilization (OEE/OLE)	KK	%	=((sum({<FiscalYear={$(=max(FiscalYear))},Date1 ={">=$(=monthstart(max({<Flag={'Actual'}>}Date1)))<=$(=max({<Flag={'Actual'}>}Date1))"}>}PtimeFinal2)
P4	Production / man / shift	KK	Nos.	num(avg({<FiscalYear={$(=max(FiscalYear))},BUDCAT={'A'},Date1 ={">=$(=monthstart(max({<Flag={'Actual'}>}Date1)))<=$(=max({<Flag={'Actual'}>}Date1))"}>}Productivity) ,'#,##0.0' )

and the output i want as under

SrNo	KPI	Pillar	Unit	Apr-17
P1	Sales	Mktg	Rs Lacs	11233
P2	Overall Plant Effectiveness (OPE)	KK	%	67%
P3	Plant effective Utilization (OEE/OLE)	KK	%	89%
P4	Production / man / shift	KK	Nos.	112

Re: Can i use dimension as expression

rahulhv1 — Mon, 30 Oct 2017 04:22:08 GMT

Thank you so much Vineeth,

i have gone through the solution which you have suggested.

Could you please refer the example i have given here and suggest me the solution for that.

I have more than 50 Dimensions and i want to have all of them in a table. I have already given the example of input & required output above.

Re: Can i use dimension as expression

Clever_Anjos — Mon, 30 Oct 2017 09:56:10 GMT

You can download the qvw example, it´s Qlik does not matter its Portuguese or English

Re: Can i use dimension as expression

MarcoWedel — Mon, 30 Oct 2017 21:18:21 GMT

Hi,

maybe helpful:

The Magic of Dollar Expansions

The Little Equals Sign

The Magic of Variables

regards

Marco