topic Building Structures? in QlikView

Building Structures?

james — Sat, 18 Apr 2009 21:41:22 GMT

All,

Has anyone had any luck building structures in qlikview on the front end? I.e. you have actuals plan variance etc, yet you need to hard code in lines in expressions to build a nice view.

Lets say you add 5 expressions that say Product Sales, expense, sales,, marketing , misc revenue

Is there any way to create the 3"columns" that have i it the Plan, the 2nd column as Actuals and than the 3rd as Variance. I cannot get this to work in a pivot form as dimensions keep adding sub layers and a straight table isnt working.

Building Structures?

Oleg_Troyansky — Sat, 18 Apr 2009 22:50:21 GMT

James,

please expand... perhaps an example could help...

From what I can understand - do you want to re-use the results of some expressions in other expressions? You can reference Expressions by number:

Column(1) - Column(2)

or you can reference them by the label:

[Actual Sales] - [Plan]

You can also save your expressions in variables and then re-use the expression using $-sign extension:

SET vSales = 'sum(Sales)';

<expr> = $(vSales)

If these are not relevant to your question - please explain in more detail.

Oleg

Building Structures?

james — Sat, 18 Apr 2009 23:54:20 GMT

Oleg,

The issue is we need to hard code in the lines to make it look good. For example, Product Sales is derived from an expression which is

Sum(If(Prod = 1, Amt))

this is used consistently down

InThe dimensions we use Type which is defined in the load, I.e. Plan, Actuals

Yet doing this it doesn not allow for you to define Actuals - Plan as those are Dimensions. Yet the only way to get a view like this is using it this way... Any ideas??

This is using the chart "Pivot Table"

Building Structures?

Oleg_Troyansky — Sun, 19 Apr 2009 04:19:39 GMT

I usually use flags to resolve situations like this . For example, instead of using expression like yours:

sum(if(Prod = 1, Amt),

I pre-calculate the flag and change the expression to the following:

sum(Amt * Prod_Flag)

To solve your second problem, you can have another set of flags - for example, Actual_Flag, Plan_Flag. Then, you can add another value for your Dimension - Plan, Actuals, Difference (let's call it ColumnName), and build your expression this way:

if(ColumnName = 'Actuals', sum(Amt * Prod_Flag * Actual_Flag),

if(ColumnName = 'Plan', sum(Amt * Prod_Flag * Plan_Flag),

if(ColumnName = 'Difference', sum(Amt * Prod_Flag * Actual_Flag) - sum(Amt * Prod_Flag * Plan_Flag)

)))

Notice that this "IF" is OK, it's outside of the sum() function, while your "IF" can be a killer, because it's inside of the sum() function, therefore it will be performed as many times as the number of your detailed records.

hope it works for you,

Oleg

Building Structures?

james — Sun, 19 Apr 2009 05:00:58 GMT

Will this understand the columns even thought they are the 4th dimension on the pivot table? They are not actual column names, jut based on a field called "Type", hich in this case is either Actuals or Plan

Building Structures?

Oleg_Troyansky — Sun, 19 Apr 2009 05:30:39 GMT

ColumnName was the field name that I came up with... In your case, use your field "Type".