Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

May be something like this -

Capture.PNG

View solution in original post

10 Replies
petter
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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.

petter
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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!

petter
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

Gotcha. Attached now!

Digvijay_Singh

May be something like this -

Capture.PNG

Anonymous
Not applicable
Author

Ah, so that's what that is for!

!.PNG

Thank you so much.

Anonymous
Not applicable
Author

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