Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Accepted Solutions
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.
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!
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...
Ah, so that's what that is for!
Thank you so much.