Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
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
Master III
Master III

May be something like this -

Capture.PNG

View solution in original post

10 Replies
MVP
MVP

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

Contributor II
Contributor II

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

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

Contributor II
Contributor II

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

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

Contributor II
Contributor II

Gotcha. Attached now!

Master III
Master III

May be something like this -

Capture.PNG

View solution in original post

Contributor II
Contributor II

Ah, so that's what that is for!

!.PNG

Thank you so much.

Contributor II
Contributor II

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