Expression as a dimension

[#1]

Hi

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?

[alexandros17]

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

[sunilkumarqv]

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

or

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

if you want label it

[#4]

Hi

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.

[alexandros17]

Could you post your document?

[maxgro]

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)

)

[#7]

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?

[sunilkumarqv]

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;

or

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

[#9]

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.