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.
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??
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:
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.
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
