topic Expression as a dimension in QlikView

Expression as a dimension

Fri, 21 Mar 2014 13:13:55 GMT

I would like to have a bar chart with document type (Invoices, Orders, Quotations) as a dimension and the Rand values as an expression. The client also want to see the sum(budget) as a separate dimension as per image below.

How does one show an expression as a separate dimension?

Re: Expression as a dimension

alexandros17 — Fri, 21 Mar 2014 13:20:29 GMT

try to add a new dimension to your data set that is add a field "document type" associated to your document (when you load or select) and use it as dimension

Re: Expression as a dimension

sunilkumarqv — Fri, 21 Mar 2014 13:24:58 GMT

In you script

make

Load sum(budget) as Sum_Budget from data source;

then add (Sum_Budget) into your Dimension

Hope your problem get solved

On Dimension tab see add calculated Dimension as sum(budget)

if you want label it

Re: Expression as a dimension

Fri, 21 Mar 2014 14:23:53 GMT

Thx for the reply. I've already got DocType as a dimension in the model so it's not a problem showing the sum of the values per doc type. The problem is showing the total of the budget as a dimension.

Re: Expression as a dimension

alexandros17 — Fri, 21 Mar 2014 14:28:48 GMT

Could you post your document?

Re: Expression as a dimension

maxgro — Fri, 21 Mar 2014 22:40:04 GMT

SCRIPT

Table: load 1500 as budget AutoGenerate 1;

CHART

dimension

ValueList('Invoices', 'Orders', 'Quotations', 'Budget')

expression

if(match(ValueList('Invoices', 'Orders', 'Quotations', 'Budget'), 'Invoices', 'Orders', 'Quotations'),

rand()*1000,

sum(budget)

)

Re: Expression as a dimension

Sat, 22 Mar 2014 05:08:46 GMT

Hi Sunil

My script looks like this

SALES:
Load
Date(DocDate) as DocDate,
FinncPriod,
ObjType,
slpcode,
slpname,
Turnover;

SQL SELECT Branch,
    DocDate,
    FinncPriod,
    ObjType,
    slpcode,
    slpname,
    Turnover
FROM dbo."XBI_SalelinesNew";

Concatenate
Load
Budget as Target,
Fincpriod as FinncPriod,
Name as slpname,
SlpCode as slpcode
;

SQL SELECT Budget,
    Fincpriod,
    Month,
    Name,
    SlpCode,
    "U_Year"
FROM dbo."XBI_SalesBudgetIncludingEmpCode";

All goes well up tp here.

I then want to do your suggestion 'Load sum(budget) as Sum_Budget from data source' to make a new dimension so -

BudgetTotals:
Load
slpcode,
Sum(Target) as TargetDimension
Resident SALES;

Then I get this error

Invalid expression
BudgetTotals:
Load
slpcode
Sum(Target) as TargetDimension
Resident SALES;

I can't see where I'm making the error?

Re: Expression as a dimension

sunilkumarqv — Sat, 22 Mar 2014 06:04:58 GMT

I noticed that

you are making "Budget as Target" field leave as it is Budget then use like below

BudgetTotals:

Load

slpcode

Sum(Budget ) as TargetDimension

Resident SALES;

you can directly use Sum(Budget ) in your calculated dimension these not make any difference

Re: Expression as a dimension

Sat, 22 Mar 2014 06:32:36 GMT

Hi Massimo

Thx for the reply - I'm getting close now but my totals in the chart is wrong - probably something small. See image below - I've added the correct values in text boxes below. When I implement your suggestion - Am I not getting budget (target) amounts per doc type and the total budget (target) as a separate column? What I need is the turnover

sum(Turnover) per doc type and the Sum(Target) as separate column like in your chart. The chart looks exactly like I want it - it's just the totals that are incorrect.