Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cschmitt
New Contributor II

Using Dynamic / Calculated Data from One Chart to Populate Another

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.

TableA.PNG

TableB.PNG

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

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

May be something like this -

Capture.PNG

10 Replies
MVP
MVP

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

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.

2017-01-13 01_38_04-Qlik Sense Desktop.png

cschmitt
New Contributor II

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

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.

MVP
MVP

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

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...

cschmitt
New Contributor II

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

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

example qs.PNG

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

table b qs.PNG

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

Thanks!

MVP
MVP

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

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...

2017-01-13 18_14_48-Using Dynamic _ Calculated Data from One Chart ... _ Qlik Community.png

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...

2017-01-13 18_16_35-Reply to Re_ Using Dynamic _ Calculated Data f... _ Qlik Community.png

cschmitt
New Contributor II

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

Gotcha. Attached now!

Digvijay_Singh
Honored Contributor III

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

May be something like this -

Capture.PNG

cschmitt
New Contributor II

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

Ah, so that's what that is for!

!.PNG

Thank you so much.

cschmitt
New Contributor II

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

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