Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
My Name is Dennis from Germany, so sorry for my bad english
I try to generate a dynamic chart table. In the dimension-tab are the facts. e.g. sales, turnover, amount of sales and so on
The facts generated in a inline table in the script and looks like this:
LOAD * INLINE [
fact_ID, fact, fact_expression
|10|, Sales, var_sales
|20|, Turnover, var_turnover
|30|, Amount, var_amount
];
I try now to get the information out of the fact_expression field into a new field.
So the global expresion converts from sum(fact_expression) to each row and get the Informations as a new Field and sum() the original field.
Sales | sum(var_sales)
Turnover | sum(var_turnover)
My only successes were to change the one expression to sum($(=fact_expression)) -> But in a $(= ) QlikView lost the dimension-Row information and get all rows together in the fact_expression..
My only chance at the momemt is to ask him what expression i want with set expression (e.g. over the fact_ID)
=sum(
$(= only({<
Fakten_ID={"|10|"}
>}Fakten_Feld))
)
Or do select in a List box a fact or fact_id
But that displays me only one and not all facts.
-------------------
So the goal: i want only one expresion which converts my fieldvalues into a real field to aggregate on it over sum..
Hope you guys understand what my problem is..
Maybe QlikView cant solve this problem at the moment.
But you want 1 expression, that kind of switches based on the current dimension value?
Then you need to create something that will end up in an expression like
=pick( match(Dimension, 'Sales', 'Turnover', 'Amount')
,sum(Sales)
,sum(Turnover)
,sum(Amount)
)
You can create this expression in your script, there is an example in the forum how to do this, just can't find it right now.
edit:
found one:
Dennis
Let me see if I understand you correctly - you would like to have a set of dynamic expressions calculated in a chart (or table) and you are trying to use set analysis to get the correct expression of each line based on a the expression number or name as a chart dimension?
This won't work I am afraid, as the set expression is evaluated once for the chart before the dimensions are created and has no concept of the dimension. The way to solve this problem is to construct a Pick(Match()) type of expression. So let's say that you have the field 'fact' as a dimension. Then you would need an expression like:
Pick(Match(fact_ID, '10', '20', '30'),
Sum(var_sales),
Sum(var_turnover),
Sum(var_amount)
)
If you have a few expressions, then this is quite easy to create. If there are many expressions (I had up to 50 in a recent document), then you will need to construct the Pick(Match()) into a variable and then use
$(vPickMatch)
to execute the variable.
HTH
Jonathan