topic Re: Using Dynamic / Calculated Data from One Chart to Populate Another in App Development

Using Dynamic / Calculated Data from One Chart to Populate Another

Anonymous — Thu, 12 Jan 2017 22:10:26 GMT

Hello,

I've created a table (TableA) in my app that has dimensions Week Ending and Keyword, and for the measures, one is a running total of Clicks by week and then another PastThreshold? where it is equal to 1 when the Running Total is the first value for that Keyword is greater than the Click Threshold, which is a dynamic variable (and in the example below is equal to 125 but can be changed by the user).

What I'd like to do, is populate another table (TableB) with only the weeks (and associated Clicks) where PastThreshold? = 1 in TableA. I cannot do this in the load script because the actual threshold is dynamic (vClickThreshold = 125 upon load, but there is an input box where users can change the value).

Here is an example in Excel of what I'm trying to do -- I'm basically trying to replicate a vlookup (or index/match), but from one Chart Table to another rather than from a Table loaded in the script.

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

petter — Fri, 13 Jan 2017 00:38:44 GMT

You can't retrieve calculated values across any charts or tables.

What you have to do is doing the calculation with the fewer dimensions - and for your case it is rather straightforward:

Create the a new table:

1) Use both the dimensions keyword and week of course.

2) Then use this expression:

FirstSortedValue( {<uid={"=Sum([Running Total Clicks])>=[~Treshold]"}>} [Running Total Clicks] , -week )

To get this expression to work you need to have a unique ID in your table. I suggest that you have a calculated value like RowNo() AS uid in your load statement in your load script. You can't use week because it is not unique.

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Anonymous — Fri, 13 Jan 2017 14:18:10 GMT

I'm not sure this will work because [Running Total Clicks] is not calculated in the load script. This value unfortunately needs to be dynamic as well because there may be certain weeks that we want to exclude.

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

petter — Fri, 13 Jan 2017 16:21:38 GMT

If you can calculate it in the first chart it shouldn't be a problem using the same or similar calculation replacing the Sum( [Running Total Clicks] ) which I suggested....

You need to provide a little bit more information if you want more help...

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Anonymous — Fri, 13 Jan 2017 16:46:33 GMT

I believe it only works in the first chart because it's showing every week. To calculate Running Total, I'm using RangeSum(Above(...)), which doesn't work when I only want one week showing. Here's what I'm talking about --

In Measure 1 I have the formula "FirstSortedValue( {<uid={"=Sum([Running Total Clicks])>=[~vClickThreshold]"}>} [Running Total Clicks] , -week )" which I realize doesn't work because [Running Total Clicks] is not a valid column name.

In Measure 2 I have "FirstSortedValue( {<uid={"=Sum(RangeSum(Above(Clicks,0,RowNo())))>=[~vClickThreshold]"}>} RangeSum(Above(Clicks,0,RowNo())) , -week )" where I replaced [Running Total Clicks] with the formula from Table A. If I delete Measure 1 and only leave the dimensions and Measure 2, all the currently selected weeks show up, which is not what I want --

I'm not sure how to attach my sample qvf file otherwise I would!

Thanks!

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

petter — Fri, 13 Jan 2017 17:21:26 GMT

You have to click on the link in the upper right-hand corner "Use advanced editor" ... to be able to attach a QVF or other type of file...

Then you will find a clickable paperclip with the text "Attach" in the lower right hand corner which can be used to attach files with...

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Anonymous — Fri, 13 Jan 2017 17:29:07 GMT

Gotcha. Attached now!

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Digvijay_Singh — Fri, 13 Jan 2017 19:26:36 GMT

May be something like this -

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Anonymous — Fri, 13 Jan 2017 20:12:51 GMT

Ah, so that's what that is for!

Thank you so much.

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Anonymous — Fri, 13 Jan 2017 21:35:32 GMT

Would you know how I can get a Total in the Totals row for Measure 1?

Re: Using Dynamic / Calculated Data from One Chart to Populate Another

Digvijay_Singh — Fri, 13 Jan 2017 21:45:27 GMT

In the measure section, select 'SUM' in total function.