Dynamic Straight Table Calculations

[#1]

I have a straight table and what to SUM() what is selected within a list box. However, within the list box is only each divisions name, such as Finance, HR, etc. When I originally imported my data from each division I used qualify, thus for a given metric such as PLAN, the field is named Finance.PLAN or HR.PLAN. Thus within my straight table I want to sum PLAN and FORECAST individually. In order to do this I have written the following, however I keep getting an error message that states: Error: Error in expression: Nested aggregation not allowed. Is there anyway to script what I'm trying to do?

=Sum(Concat(getfieldselections([Divisions])&' .Plan'))

[krishna_2644]

1. use 'Always one selected value' for a list box to limit the selections to only one at a time.

2.previous attachment has answer for this. (for actual and forecast fields to get displayed, provide some 4-5 records of data)

3. it the field has been qualified in the script, then try writing

=if(getselectedcount(division) >=1,concat({1<division=P(division)>}distinct  'Data.Metric.' & division),0)

[qlikviewwizard]

Hi Michael Palumbo

Please post the sample data with the application to demonstrate your issue. So that it helps the folks for easy to understand and provide you right solution. Thank you.

[jonathandienst]

It sounds to me that your data model is fundamentally misaligned with what you are trying to report. A well designed model might have complex transformations in the load script. but the front end will require simple expressions with easily configured set expressions and filters.

You may well be able to solve your immediate problem by a complex solution in the front end, but IMHO you are solving the wrong problem.

If you would like help in this area, I suggest that you create a new post and upload the application, or at least a sample that includes the load script and (if possible) sample source data in a spreadsheet, text file or in an inline load in the application.

[#5]

Attached you will find a sample of what I am trying to do. This sample is simply for one division, when in reality, I will need to account for selections in every division possible. When a division is selected the straight table should be able to read what is selected and display that divisions metrics, in addition to their plan, actual, and forecasts. Each of the division is housed within a separate tab in script editor, and further, are all qualified. Thus within the straight table a division will be selected, but in order to know filter and display the divisions metrics and plan, actuals, and forecasts, the only thing I can think of using is some sort of CONCAT() function, such as the ones below. Any suggestions? Is it not possible to CONCAT() strings of text to equal an actual field name to then aggregate? Thanks.

Plan:

=IF(getselectedcount([Divisions]) >=1 , Sum(concat({1<[Divisions]=P([Divisions])>}distinct  [Divisions]&' Data.Plan')),0)

Actuals:

=IF(getselectedcount([Divisions]) >=1 , Sum(concat({1<[Divisions]=P([Divisions])>}distinct  [Divisions]&' Data.Actuals')),0)

Forecast:

=IF(getselectedcount([Divisions]) >=1 , Sum(concat({1<[Divisions]=P([Divisions])>}distinct  [Divisions]&' Data.Forecast')),0)

[#6]

Any luck?

[krishna_2644]

See attached.

you can finish up the rest.

i did Engineering with data.

Sales,dixiechopper,HR with no data  - Insert data for them.

Also modify the rest of the expressions accordinly.

i have written expressions for Plan,Actual,@--.

Thanks

[#8]

Thanks for the fast reply, a few questions:

My original fields are named Engineering Data.Metric, etc. as opposed to Engineering.Metric, this space is causing some confusion within my dimension and expressions, thus displaying an error message as opposed to the actual values. Thus, I would have to write something as follows:

=$(=if(getselectedcount(Divisions)=1,'Sum(' & concat(Divisions) & ' Data.' & 'Plan' & ')'))

or

=$(=if(getselectedcount(Divisions)=1,'Sum(' & concat(Divisions) & chr(32) & 'Data' & '.' & 'Plan' & ')'))

Further, why did you LOAD INLINE all the metric names on the third tab? Thanks again.

[krishna_2644]

LOAD INLINE all the metric names on the third tab  was for my testing purposes. you can delete it.

The other inline loads for HR,sales  etc, you can remove them and put your own data script.

let me know if thats what you wanted in the qvw?

Thanks

[#10]

Yes it is what I wanted but see my dilemma above regarding naming and spaces

[krishna_2644]

rewrite the exact columns names in the qvw and attach it to me.

Will figure it out.