Date | # of Participants
4/1/2016 | 400
4/2/2016 | 500
4/3/2016 | 625
I want a field that will calculate the difference from the day before. So for 4/2/2016, it would show 100 (500-400). For 4/3/2016 it would show 125 (625-500). Please let me know what formula to use. Thanks!]]>
This is all done using for loops which aren't quick and results in the content view taking about 7000ms on a fast machine to load and on slow machines, Omniscope times out and the execution doesn't complete so the content view is blank.
I am not sure how to use the RANK function to do this (if indeed it can) as the docs are a little confusing and there aren't many decent examples. I would like to keep the calculation in the content view if possible.
Thanks for your help in advance,
]]>
In the dataset below, missing values in the field [Ctr] should take their value from the previous day OR the last filled in date, for the same Campaign. A Sort operation block can be used for better transparency, to order [Date] and [Campaign].
Formula below will pick the last available value for each combination of Date/Campaign:
IF([Ctr]=null,
SUBSET_LASTNONNULL([Ctr], SUBSET2([Campaign],[Rank Date],[Campaign],[Rank Date],"=","<")),
[Ctr])It relies on the [Date] ranking:
RANK([Date],[Date],true,false, SUBSET([Campaign]))For each campaign ranking runs from 1 to n, with the earliest date being ranked 1st.
In the case where their is no logical ranking criteria to sort inside the data set, the record number field may be useful (see below)
]]>And the last requirement is to show the difference for every brand to the year before.
So far I work it out for all brand which are in the data with formulas in the fields.
However I would need more dynamic.
Is there a way to get it are you working on it?]]>
]]>
I have a medium sized data set ~2500 records. My filter is a checkbox filter (I can select any number of records, filter field is non-repeating text). The idea here is to make this filtered data a subset as I want to perform some operations on this filtered data.
The end goal is to assign a rank to each record of this randomly selected subset of data. So, if an additioal record is selected, the ranks may change based on the field value of the new record.]]>
For one column I want to calculate average of top 20% values of it. How to write the formula for the same?]]>
I intend to perform some basic calculations (mean) on a subset of the records in a table. I used Subset_Mean(field name, RowIndexSubset). However, it returns a null value. The RowIndexSubset is calculated properly (verified it using Subset_UniqueCount).
The main idea is to get the top 10% of the total records and then calculate the mean of the values in a specific field. This total records will change based on a filter selection (this filtering is working fine).]]>
See attachment for reference.
Thanks..]]>
Rank function Syntax: RANK(value, field, isAscending, includeNulls, dataSubset)
Ranking Spend=
RANK([Spend])
# simple Rank comparing all values in the Spend field
Rank Spend per Week=
RANK([Spend], [Spend], false, false, SUBSET([Week]))
#Ranking Spend values inside each Week
SubRank Clicks =
RANK([Clicks], [Clicks], false, false,subset([Rank Spend per Week] ))
# secondary rank field, based on the Clicks value. Meaningful when multiple rows share the same [Rank spend per Week] value
Spend per Keyword=
SUBSET_SUM([Spend], SUBSET([Keyword]))
# total Spend for each Keyword
[One]=1
# basis for running total indexing
Running total per Keyword:
RUNNINGTOTAL([One], SUBSET([Keyword]))
Rank Totals per Keyword=
IF([Running total per Keyw]>1, null,
RANK([Spend per Keyword], [Spend per Keyword], false, true, SUBSET([Running total per Keyw],1,"=")))
# ignoring the values greater than 1, rank compares only the subset sum values where [Running total per Keyw] is 1, ensuring that only one value per category is compared. ]]>
I'd appreciate if the record filter was changed so there is a setting to grab the top ten (or N) items for each category. At present this isn't possible and you have to do four steps to replicate this.
As an example, I'm working with Google Analytics data for multiple websites (using the API connector which is very handy) and I'd like to see the top 10 landing pages for each site. Not the most exciting request, but I imagine this has applications elsewhere. Thanks]]>
Thanks for the help, Brian]]>
I have been asked by quite a few of our clients and I think this feature would be a very powerful addition to the views in DataExplorer.
The simplest use case is as follows:
I have a Bar/Line view with measure X and split by one single field [Field1]. With this new feature, I'd be able to say, "Show only top 10 entries" and an automatic filter will be attached to that Bar/LIne view.
Building on the simplest case, we can think of further generalisations like:
1. Configurable number of entries, i.e. 'top/bottom 'N' entries', N to be chosen by user
2. Top/Bottom 'N' entries, rather than only top entries
3. Extension of the same concept over aggregated measures
4. Function support to enable the same feature through formulae
Please let me know if any of this is possible.
Cheers! Indranil]]>
A good example would be to look at the Visokio example on http://www.visokio.com/kb/subset-functions-2-5:
How do I add a column to it that will show the number 1 against John Swires and 2 against Anne Campbell, reflecting their ranking in terms of total spend?
Thanks!
Gil]]>
After sort I want to display only top 5 records in final view of table. I am not able to find anything to do that. Can someone please help on limiting the records to 5.
Please let me know if you need more information.
Thanks. ]]>