Evaluating Expression in PivotTable

Fri, 17 May 2013 11:01:48 GMT

Hi,

I would like to re-produce the following table:

Entity	Omzet	Omzet
	Actual	Budget
Company A
Company B

The expressions for each column (Revenues, Direct kost, Gross profit and Ebit% x Actuals / Budget) are stored in Excel

The expression table looks something like that:

Rapportage id	Sectie	Kolom naam	Kolom label	kolom Expression	Kolom formaat
1	Actual	Omzet	Omzet	sum( {< [Verdichting1]= {'Total Revenue from Services'}, [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} [#Transact bedrag])	#.##0
1	Actual	Marge	Marge	sum( {< [Exec P&L Heading]= {'Gross Profit'}, [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} [#Transact bedrag])	#.##0
1	Actual	Kosten	Kosten	$(=only({<Sectie = {'Actual'},[Kolom naam] = {'Marge'}>}[kolom Expression])) - $(=only({<Sectie = {'Actual'},[Kolom naam] = {'Resultaat'}>}[kolom Expression]))	#.##0
1	Actual	Resultaat	Resultaat	sum( {< [Exec P&L Heading] = {'Income before taxes'}, [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} [#Transact bedrag])	#.##0
1	Actual	FTE	FTE	sum( {< [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} [# totaal FTE])	#.##0,0
1	Actual	Ebit%	Ebit%	$(=only({<Sectie = {'Actual'},[Kolom naam] = {'Resultaat'}>}[kolom Expression])) / $(=only({<Sectie = {'Actual'},[Kolom naam] = {'Omzet'}>}[kolom Expression]))	#.##0,0%
1	Budget	Omzet	Omzet	sum( {< [Verdichting1]= {'Total Revenue from Services'}, [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} #Budget)	#.##0
1	Budget	Marge	Marge	sum( {< [Exec P&L Heading]= {'Gross Profit'}, [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} #Budget)	#.##0
1	Budget	Kosten	Kosten	$(=only({<Sectie = {'Budget'},[Kolom naam] = {'Marge'}>}[kolom Expression])) - $(=only({<Sectie = {'Budget'},[Kolom naam] = {'Resultaat'}>}[kolom Expression]))	#.##0
1	Budget	Resultaat	Resultaat	sum( {< [Exec P&L Heading] = {'Income before taxes'}, [F einddatum]={"=$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} #Budget)	#.##0
1	Budget	FTE	FTE	sum( {< [F einddatum]={"$(=$(=$(getMaxDate('P<->(P-1)'))))"} >} [# totaal FTE])	#.##0,0
1	Budget	Ebit%	Ebit%	$(=only({<Sectie = {'Budget'},[Kolom naam] = {'Resultaat'}>}[kolom Expression])) / $(=only({<Sectie = {'Budget'},[Kolom naam] = {'Omzet'}>}[kolom Expression]))	#.##0,0%

Rendering the expression in the pivot chart shows the right expression in the right place

Pivot setup

Dimensions:

1.RPT_dim_hdr (The king field group, CNIT, ect...)

2.Rpt sectie (Actual, Budget)

3.Kolom naam (Omzet, Kosten, Marge, ect...)

Expression

[kolom Expression]

When i try to evaluate the expression,

$(=[kolom Expression]) I get nothing...

Can this actually be done?

Dror

Re: Evaluating Expression in PivotTable

pljsoftware — Wed, 12 Nov 2014 15:59:26 GMT

Hi Dror,

have you solve this question? I have the same problem, when I try to evaluate the expressione with $(=Only(Formula)) Qlikview write null value. If I write Only(Formula) I look the formula.

I have try to write expression $(=Formula) but this not work, it work only if I select a single value for Classificazione and Categorizzazione.

I tried to write the expression Count(Formula) to test how expression it have for single cell and I receive always 1.

I add my application files.

Thanks in advance for your help.

Regards

Luca Jonathan Panetta

PLJ Software

topic Evaluating Expression in PivotTable in QlikView

Evaluating Expression in PivotTable

Re: Evaluating Expression in PivotTable