Newbe question - how to dynamicly change pivottable expressions based on measures list ?

[#1]

Hi all,

I'm expirienced Olap and BI developer and now i'm starting to learn QlikView.

In Olap, it is very easy to create a pivot table where the measures (i.e expressions) can be dynamicly selected by the user.

I'm trying to achieve the same in qlik view but no luck.

what i did is:

1. I created an in-line table at the script, containing my measure names and expressions:

LOAD * INLINE [

    MeasureName,Formula

    SN Count,count(distinct UnitItemID)

    Symptoms Count,Count(Distinct SymptomSkey)

    Defects Count,Count(Distinct DefectSkey)

];

2. I then created a ListBox displaying the Measure Name and Formual fields.

3. I created a variable named "SelectedMeasures", and gave it the following expression:

GetFieldSelections("Formula")

4. On the PivotTable, i selected the required dimensions, then at the Expressions, i added :

=SelectedMeasures

The expected result is that when i select a specific measure from the measures listbox, the

PivotTable will display the value of the selected measures.

The problem is that it displays the variable value and not calculating it as an expression.

For example, if i select count(distinct UnitItemID) from the measures list, the pivot table display's the string count(distinct UnitItemID)

instead of calculating it.

I need somthing like the Javascript eval function.

Any help will be appreciated.

[#2]

I did something like this in a chart. Should be similar for a pivot. Requires a bit more coding though.

if(GetFieldSelections([Show Chart])='Quote~Number of Quotes', Num(Count(DISTINCT{<[Year Entered]={$(=Year(Today()))}>} [Quote Number]),'#,##0'),
if(GetFieldSelections([Show Chart])='Quote~Quote Amount', Num(Sum({<[Year Entered]={$(=Year(Today()))}>} [Quoted Amount]),'$#,##0.00;-$#,##0.00'),
if(GetFieldSelections([Show Chart])='Quote~Quotes Over 100K', Num(Count({<[Year Entered]={$(=Year(Today()))},[Quoted Amount]={'>=100000'}>} [Quote Number]),'#,##0'),
if(GetFieldSelections([Show Chart])='Quote~Quotes Under 100K', Num(Count({<[Year Entered]={$(=Year(Today()))},[Quoted Amount]={'<100000'}>} [Quote Number]),'#,##0'),
if(GetFieldSelections([Show Chart])='Quote~Quotes Lost', Num(Count({<[Year Entered]={$(=Year(Today()))},[Reason Type]={'L'}>} [Quote Number]),'#,##0'),
if(GetFieldSelections([Show Chart])='Quote~Lines Quoted', Num(Count({<[Year Entered]={$(=Year(Today()))}>} [Quote Line]),'#,##0'),
if(GetFieldSelections([Show Chart])='Quote~Lines Ordered', Num(Count({<[Year Entered]={$(=Year(Today()))},[Line Ordered]={'1'}>} [Quote Line]),'#,##0'),
if(GetFieldSelections([Show Chart])='Order~Number of Orders', Num(Count({<[Year Ordered]={$(=Year(Today()))}>} [Order Number]),'#,##0'),
if(GetFieldSelections([Show Chart])='Order~Number of Lines', Num(Count({<[Year Ordered]={$(=Year(Today()))}>} [Order Line]),'#,##0'),
if(GetFieldSelections([Show Chart])='Order~Order Amount', Num(SUM({<[Year Ordered]={$(=Year(Today()))}>} [Order Amount]),'$#,##0.00;-$#,##0.00'),
if(GetFieldSelections([Show Chart])='Call~Follow up Calls',Num(Count({<[Year Call]={$(=Year(Today()))},calltypecode={'Q-FLUP'}>}[Call Text]),'#,##0'),
if(GetFieldSelections([Show Chart])='Call~New/Low Activity Calls',Num(Count({<[Year Call]={$(=Year(Today()))},calltypecode={'M-CFLUP','MEMAIL','M-CCALL'}>} [Call Text]),'#,##0'),
if(GetFieldSelections([Show Chart])='Call~Total Calls',Num(Count({<[Year Call]={$(=Year(Today()))}>} [Call Text]),'#,##0'),
if(GetFieldSelections([Show Chart])='Customer~New Customer',Num(sum({<[Year Changed]={$(=Year(Today()))}>}if([Customer Type]='CUS' and (WildMatch(logtext,'*-> CUS*') or WildMatch(logtext,'*New Record*')),1)),'#,##0'),
if(GetFieldSelections([Show Chart])='Customer~New Suspect',Num(sum({<[Year Changed]={$(=Year(Today()))}>}if([Customer Type]='SUS' and (WildMatch(logtext,'*-> SUS*') or WildMatch(logtext,'*New Record*')),1)),'#,##0'),
if(GetFieldSelections([Show Chart])='Customer~New Prospect',Num(Sum({<[Year Changed]={$(=Year(Today()))}>} if([Customer Type]='PRO' and (WildMatch(logtext,'*-> PRO*') or WildMatch(logtext,'*New Record*')),1)),'#,##0')
))))))))))))))))

I have a list box based on an inline table with always one selected value set. Based on what the user selects, the correct formular is calculated.

[#3]

Yeah, i did something similar,however, this affects only a single Pivot Table column.

It does not hide / show other pivot table columns per selected measures in the Measures list.

If only PivotTable had conditional show section for expressions (as exists in Strait table)…