topic Re: Dynamic Straight Table Calculations in QlikView

Dynamic Straight Table Calculations

Thu, 23 Jul 2015 15:05:47 GMT

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'))

Re: Dynamic Straight Table Calculations

Qrishna — Thu, 23 Jul 2015 15:39:02 GMT

Not sure you want this kind but See attached doc.

Re: Dynamic Straight Table Calculations

Thu, 23 Jul 2015 17:32:59 GMT

Yes, however, when I select the division, it sum() the PLAN for every metric, when in actuality I want to select a division, and when selected it will compute PLAN for each metric in separate rows of the straight table

Re: Dynamic Straight Table Calculations

Qrishna — Thu, 23 Jul 2015 19:06:42 GMT

post some sample data.

Re: Dynamic Straight Table Calculations

Qrishna — Wed, 29 Jul 2015 16:33:32 GMT

Any luck?

Re: Dynamic Straight Table Calculations

Thu, 30 Jul 2015 13:29:44 GMT

No, still no luck, I have 10+ divisions, and only want them to display 2-3 calculations only when selected

Re: Dynamic Straight Table Calculations

Qrishna — Thu, 30 Jul 2015 14:17:21 GMT

post some sample data.

Re: Dynamic Straight Table Calculations

Kushal_Chawda — Thu, 30 Jul 2015 14:19:43 GMT

I didn't understand your question. Can you please explain with example

Re: Dynamic Straight Table Calculations

mvanlutterveld — Thu, 30 Jul 2015 14:28:26 GMT

Hi Michael.

IMHO The solution should be in the script and not in the front end calculations. Create a fact table with all the metrics you need and the key fields to the dimensional tables like Division, Calendar. Advantage of this approach is that you don’t have to qualify your fields.

Cheers,

Michiel

Re: Dynamic Straight Table Calculations

Thu, 30 Jul 2015 17:53:47 GMT

Its a bit difficult to post all the data but a screenshot is below. Here is what I am trying to accomplish:

1) Limit selections within Divisions to one, and once one is selected it dynamically updates the straight table

2) When the division is selected, the straight table will display that divisions metrics, in addition to the plan, actual, and forecast for each respectively

3) Each division and metrics are in separate tables, thus have been qualified, so something needs to follow along the lines of

=IF(GETSELECTEDCOUNT([Divisions]) =0,0, CONCAT(GETFIELDSELECTION([Divisions])&' Data.Metric')

Re: Dynamic Straight Table Calculations

Qrishna — Thu, 30 Jul 2015 18:08:30 GMT

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)

Re: Dynamic Straight Table Calculations

qlikviewwizard — Fri, 31 Jul 2015 13:52:02 GMT

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.

Re: Dynamic Straight Table Calculations

jonathandienst — Fri, 31 Jul 2015 14:02:45 GMT

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.

Re: Dynamic Straight Table Calculations

Fri, 31 Jul 2015 14:54:22 GMT

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)

Re: Dynamic Straight Table Calculations

Fri, 31 Jul 2015 15:17:52 GMT

Any luck?

Re: Dynamic Straight Table Calculations

Qrishna — Fri, 31 Jul 2015 16:53:24 GMT

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

Re: Dynamic Straight Table Calculations

Fri, 31 Jul 2015 17:29:23 GMT

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' & ')'))

=$(=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.

Re: Dynamic Straight Table Calculations

Qrishna — Fri, 31 Jul 2015 17:52:48 GMT

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

Re: Dynamic Straight Table Calculations

Fri, 31 Jul 2015 18:12:10 GMT

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

Re: Dynamic Straight Table Calculations

Qrishna — Fri, 31 Jul 2015 18:15:46 GMT

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

Will figure it out.