Tagged with subset_functions - Visokio Forums

Subset formula: Calculating % change over time

Phill — Tue, 28 Feb 2017 05:59:24 -0500

I am looking for a formula to calculate the change in number of customers using a certain product over 3 time periods. I have the total number of people in my dataset; which is also the sum of people in 3 quarters (Q1 2016, Q2 2016, Q3 2016).
I would like to show the % change in number of people using the product from the previous quarter.
How would I do this?

Subset formula: 7 Day Moving Average calculation

mttwnxn — Mon, 30 Jan 2017 13:06:59 -0500

Hi - I was wondering if anyone could help. What would be the best way to calculate a running average over a moving 7 day window? I want to use this to help create a simple forecast to determine, at the current rate, what would be the closing position position be at the end of the current week or month. Essentially answering questions such as: "if my spend over the past 7 days is maintained, how many ad impressions would I have delivered in February 2017"

My data would be structured simply as below:

Date | Market | Campaign | Channel | Impressions | Clicks | Spend
01/01/17 | UK | ABC | ABC | 111 | 111 | 1111

Formula field: Omniscope Freezes with subset_uniquecount

daniel — Mon, 23 May 2016 10:50:30 -0400

I specifically can't seem to use subset_uniquecount in a formula field without it freezing up Omniscope.

All I am doing is trying to create a rate field of:

Subset_sum([Dementia Register])/Subset_uniquecount([Practice Code])

Version 2.9 b1823 on a Win 10 desktop and also tested on a win 10 laptop. Problem persists in both.

Subset formulas - calculating % of Total per category

MPatel — Thu, 12 May 2016 11:34:25 -0400

Hi,

I have survey data and i want to show data by the percentage of control and exposed respondents. The data is categorical.

Column A; Column B; Column C
Respondent 1 ; Control; Competitor A
Respondent 2 ; Control; Competitor B
Respondent 3 ; Exposed; Competitor C
Respondent 4 ; Exposed; Competitor A
Respondent 5 ; Exposed; Competitor B
Respondent 6 ; Exposed; Competitor C

The chart needs to show two bars for Competitor A, one control and one exposed. Control would be 50%, Exposed would be 25%. For competitor B it would show Control 50%, Exposed 25%. For competitor C it would show Control 0%, Exposed 50%.

Any idea how to do this??

Time Series Growth Calculation - Getting Incremental Numbers from Cumulative Total

MPatel — Thu, 12 May 2016 17:53:16 -0400

Hi,
I have data that's coming in cumulatively:

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!

Functions: Interactive format to explore and find Omniscope functions

paola — Wed, 10 Feb 2016 11:12:10 -0500

Here is a new way to look at functions in Omniscope… Or quickly find the best one for the job at hand.

Use filtering, selection and search box to browse through functions organised in different categories. There are also functions that fall into two or more categories – grey cells in the Tile view.
Take few minutes to look at different text-processing, geographical, data or date transformation functions – you are guaranteed to find something that will make your life easier!

Open the file in a browser:
https://omniscope.me/FunctionsWEB.iok/

SUBSET Functions: Calculating sum within a date range

DDrob — Fri, 01 May 2015 11:26:48 -0400

Hi guys

I haven't posted here before, I usually manage to work my issues out if I look at it for long enough but this one is just puzzling me. I feel like I'm doing everything right but I'm not getting the desired outcome.

My data is sales data, split by day and email address, where each row is a sale. I'm trying to create a new field which determines whether the sale was from a new customer (this was their first purchase in the last year), or an existing customer (purchased in the last year).

My steps were as follows:
1. Create a new field (StartDate) which is a DATEADD of -1 year to determine the lower end of my existing customer bracket.
2. Create a new field which is a function of SUBSET_SUM, SUBSET3 where Email=Email,Date>StartDate,Date
This will give me a sum of the sales where the email has appeared previously within my date range, and I can take 0 values as new and anything else as existing.

Now, my new field isn't returning anything and I can't work out why.

Hope you can help.

Thanks

P.S. I will attach the formula I used when I get access to my file again, although I'm pretty sure my syntax is correct.

Missing Data: Automatically filling in the blanks

paola — Wed, 28 May 2014 13:58:50 -0400

When working with an incomplete dataset that contains blank cells, which should be populated based on the values of neighbouring cells above, the process of filling in the missing values can be automated in the DataManager to fully prepare the data for visualisation.

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)

Filtering: Dynamic record counts on filtered data?

dpawley — Fri, 29 Aug 2014 07:34:19 -0400

Hi

I have a large dataset of 400k records over 12 months, by supplier, by product, by outlets. What I would like to get to is "Product A is in 50% of the outlets, over 12 months. However in August it was in 80%" from the dataset.

I can separate out the data in a chart that ranks the products by volume top to bottom over the past 12 months. I have used Unique_Count to get the number of outlets that are stocking Product A which is great but I want to divide this number by the total number of outlets in the dataset and have it change if I was to select one month, or 2 months (i.e. filter).

Thanks

Danny

Idea: Can Subset( ) work on filtered data?

walsh — Mon, 18 Jul 2011 17:01:29 -0400

I'm monitoring project status. Each project comprises a number of activities that are assigned a RAG (Red/Amber/Green) status. If any of the project's constituent activities is marked as Red, then the project status is set to Red. If any activity is Amber (and none is Red), the Project status is Amber. I assign numeric values to each Activity's RAG status and select the maximum value (SUBSET_MAX()) for each project to set the colour of its Project (RAG) Status.

In the attached snippet, I have one project and three activities. The activities are, respectively, Red, Amber and Green. When I filter out the completed activity (Red), I was hoping to see the Project status change, as now there's only a Green and an Amber in play. That doesn't happen - the overall project status stays Red, even when I show only the open activity, which is Green.

Is there a way to make SUBSET() functions recalculate on just filtered data? Or am I going the wrong way about this?

Formulae: Calculating the average of a 'top-N' subset?

tagarw — Mon, 01 Sep 2014 04:04:47 -0400

Hi,
I have a Table view in which data is changing according to the filters. For some selection suppose it has n number of records and 3 columns.

For one column I want to calculate average of top 20% values of it. How to write the formula for the same?

Formulae: Subset Functions-Record count within lookback window/ Records between

alexandervolk — Mon, 11 Aug 2014 05:31:16 -0400

I have a set of timestamped data (page visits) in which - for each record - I want to calculate the number of page visits within the last N minutes. I have a device which sets N as a parameter, defaulted to 5 minutes.

In a second field next to "timestamp", I am calculating "timestamp-N" for the lookback window. My initial idea was to use the RECORDCOUNT formula and for each record, calculate the record count between MIN[timestamp-N] and MAX[timestamp]. That doesn't seem to work ...or at least I can't get the formula right.

Are there other ways to get the results I need?

Thanks for your help!
Alex

Content View: Insert Multiple Subset Record Count into 1 Table?

MattBoxall — Thu, 12 Jun 2014 04:56:00 -0400

Hi Team!

I have multiple source excels plugged into Omni, I've filtered them to get the desired numbers and then saved them as 3 queries.

"Number of Plans"
"Number of New Plans"
"Number of Closed Plans"

I'm currently making a table in Content View where a cell has a function inserted that will do RECORDCOUNT(SUBSET) and return the filtered number from my query selected, which works when the query is selected via data subset selection. i.e. Subset: "Number of Plans".

I was wondering how to get all 3 query numbers into one content view via a table/3 cells as I can only insert a formula recordcount(subset) and I don't know how to reference other queries.

currently I have got 1 content view per query and it looks a bit separated.
Much thanks,
Matt

Subset functions: identify earliest Date(s) in a field?

Valentina — Tue, 10 Jun 2014 09:47:02 -0400

Hi all,

Assume I have 2 columns: column 1 is Date & column 2 is ID. Note that each ID might have more than one date.

I want to create a third column showing the oldest date for each id. See example below

[ID] [Date ] [Result]
4 3/5/2014 3/5/2014
4 4/5/2014 3/5/2014
7 16/5/2014 16/5/2014
8 7/5/2014 7/5/2014
8 15/5/2014 7/5/2014

Is this possible for this to be done using Subset_min function?

Formulae: Subset functions

Valentina — Wed, 23 Apr 2014 07:41:25 -0400

Hi

I am trying to use subset functions to do the following but it seems that more functions are needed.

I have a column with ID's and another column showing the steps of the ID. The Temp column shows the temperature of the ID at each step. Ideally I want to create a fourth column (like First step temp below) showing the temperature of the First Step only, across all same ID cells.

Hope it makes sense. Any ideas are welcomed. Thanks

ID Steps Temp First step temp
A First Warm Warm
A Middle Cool Warm
A Last Warm Warm
B First Warm Warm
B Last Warm Warm
C First Cool Cool
C Middle Cool Cool
C Middle Warm Cool
C Last Cool Cool

Formulae: Subset_sum formula error?

PSliz — Wed, 02 Apr 2014 05:53:25 -0400

Hi,

Please could someone help with the following formula;

(subset_sum([% Popn], subset2([Reach Cat],[ctyName], [Reach Cat], 'Warwickshire','=','='))/subset_sum([% Popn], subset([Reach Cat])))*100

In words: first I am trying to sum the resident population ([%Popn]) for each of the Reach categories (10 categories, [Reach Cat]) where the county ([ctyName]) is Warwickshire. Then I want to divide this number by the sum of the resident population in each of the Reach categories across the whole table.

I keep getting the error "Cannot use field value references. This formula is evaluated in the context of multiple records, without any specific individual records." The problem seems to be with the second "Reach_Cat" in subset2.

I am trying to apply this formula in the measures field of a bar/line chart. The chart will have Reach_cat categories along the x-axis.

%Popn is a decimal number field.
Reach_Cat and ctyName are both category fields.

Please could someone tell me;
1. Exactly what this error means, as I have seen it before but don't know exactly what problem it is indicating.
2. How to fix this formula error.

Thanks in advance for any help,

Formulae: Creating custom interval sizes?

DBuzacott — Thu, 06 Feb 2014 01:27:24 -0500

Hi,

We have a number of views that are split by value (decimal numbers). Currently we split this by a fixed interval It would be useful to be able to customise interval ranges to accommodate large value ranges. For example, we may want to split by value ranges such as 0-100, 100-1k, 1k-100k etcetera. FYI, in general we measure a sum (aggregation) of a cost field for a given category (i.e. number of categories with a total spend in a given range).

We can do this a little with formula fields (IFs and SUBSETs), but this is limited when applying filters. For example, if we set up a calculation for total spend, this requires us to fix in the calculation the fields over which it is calculated. So we may set the calculation over subset([Site]), but then if we filter the view by another field (e.g. [Year]) then it doesn't update the calculation field accordingly.

Ideally, we would be able to set this up in the view, rather than with a calculation field. The simplest method that I can think of is to allow custom interval ranges (i.e. not fixed-width) in the "Split" menu.
Is this a possible addition?

Thanks, Daniel

Subset Functions: Syntax for Multiple Criteria on same field?

Master_Chief — Wed, 06 Nov 2013 10:33:24 -0500

What is the correct Syntax for performing an operation (e.g. SUM) using multiple criteria on the same field (e.g. > 5 and <10)?

Formulae: Subset Functions - conditionality?

davedunckley — Tue, 10 Sep 2013 03:27:47 -0400

Hi I am having a little trouble with Subset functions...

What I would like to be able to do is to work out the total per charterer in the last 52 weeks.

I have created a field called [islast52] that gives me a 'y' or 'n' depending. Then I am trying the following to get the results. However, I cant quite get it right. Some help would be great.

SUBSET_SUM([Total USD Fixture], SUBSET([Charterer.fixture]),
subset([IsLast52.fixture],"y","=")))

Thanks, Dave

Formulae: Using SUBSET RECORDCOUNT?

iazcac — Wed, 19 Jun 2013 12:18:19 -0400

Hi, Hope someone can help a first time poster!
I have a data set in Omniscope including the fields

[Category] | [Country] | [EmployeeID]

In English, I want to count the number employees for each country where category = X. I have tried if, subsets and recordcount and just cant get it to work! Is probably simple, but any help appreciated

C

Formulae: SUBSET(MAX) Extract data with latest date?

Nemesis — Sun, 26 May 2013 21:57:44 -0400

How to extract data from database which has duplicates in column [A], duplicate data in column [B] and unique values (dates) corresponding to each row in column [C]. I want to extract the row which has latest date corresponding to unique value in column [A].

[A]| [B] | [C]

a | 1 | 10/12/2012
a | 2 | 10/13/2012
c | 1 | 10/14/2012
d | 2 | 10/15/2012
s | 3 | 10/16/2012
d | 3 | 10/17/2012
f | 212| 10/18/2012
g | 4 | 10/19/2012
e | 2 | 10/20/2012
a | 1 | 10/21/2012
e | 32 | 10/22/2012
a | 32 | 10/23/2012
b | 32 | 10/24/2012
a | 3 | 10/25/2012
b | 23 | 10/26/2012
c | 2 | 10/27/2012
c | 32 | 10/28/2012

Like "a" having latest data of:

"a 3 10/25/2012"

and similarly for all other unique values from column [A]. Any suggestions please!!

Formulae: SUBSET - testing the same field for different values?

nicci113 — Wed, 22 May 2013 09:49:48 -0400

Hi There, Can somebody please help?

If I want to select [NewField2] and look for values 'CARBRAND' then pick corresponding value from [Spend] and ADD that to where [Newfield2] value is 'PUREBRAND' and the value from [Media..] Highlighted in attachment

Subset functions: Examples of syntax?

nicci113 — Tue, 21 May 2013 06:49:44 -0400

Can we merge certain specific rows? and combine into 1 new row? e.g.
within row you have
abc
def
ghi
I want to merge the content of abc and ghi and call it =abc2, at the same time can i use the total of this new row abc2 and use the total for further calc and ignore ghi completely...

Idea: Formulae - Specifying ALL DATA subset in a formula?

mohamed — Fri, 27 Jul 2012 06:15:21 -0400

How can I have a formula to look at the ALL DATA instead of the filtered data? For example, SUBSET_SINGLETON([Latest month]) in my file always returns May. But if the filtered data is zero, it returns "no value". Is there way of telling the function to look at the ALL data, so something like this SUBSET_SINGLETON([Latest month], subsetAll())

SUBSET Function formulae - new operators (2.8+)

steve — Thu, 04 Apr 2013 08:25:28 -0400

Newly introduced, the optional 3rd "operator" argument of SUBSET() can now accept text-specific operators (in addition to the implicit default "=" and other existing numerical operators such as ">="):

"contains"
"does-not-contain"
"ends-with"
"starts-with"

For example, this identifies all people whose surname begins "Mac":

SUBSET([Surname], "Mac", "starts-with")

Analytics: Recency Frequency Value?

grahamb — Fri, 15 Mar 2013 08:46:47 -0400

I am unsure if this function is planned but it would be good if it could be added. A function for automated recency, frequency, value analysis within the software so you can select a transaction table relating to customers' orders and then decide the number of categories per measure - say 5, then generate for each customer three new fields grading recency 5 to 1, frequency 5 to 1 and value 5 to 1. This will then create 125 segmentation groups for targeting. If 3 categories selected, then that will give 27 segments. With this analysis as well as coding each customer record with the RFM bands it would be useful to create a pivot table to show recxency by frequency, and within each cell a bar chart breaking out the value measure

Formulae: New extensible SUBSET function syntax

steve — Tue, 19 Apr 2011 10:23:03 -0400

The SUBSET function has now been complemented in recent 2.6+ builds by:

- INTERSECTION - http://www.visokio.com/functions-guide#INTERSECTION
- UNION - http://www.visokio.com/functions-guide#UNION
- INVERSE - http://www.visokio.com/functions-guide#INVERSE

These greatly improve the simplicity, flexibility and power of subsets in formulas. They replace the complicated SUBSET2, SUBSET3 (etc.) functions, and more. Instead of these, use INTERSECTION(SUBSET(...), SUBSET(...), SUBSET(...)).

Examples:
The sum of Sales for all records with a different currency to the current record:
SUBSET_SUM( Sales, INVERSE(SUBSET(Currency)) )

The number of records with yield < 0.1 and in USD or GBP:
RECORDCOUNT(INTERSECTION(
UNION(SUBSET(Currency, "USD"), SUBSET(Currency, "GBP")),
SUBSET(Yield, 0.1, "<")
))

See Subset Functions Guide: http://www.visokio.com/kb/subset-functions

Formula performance and usability improvements (2.8+)

steve — Mon, 22 Oct 2012 08:34:49 -0400

INTERSECTION performance

From tonight's build of 2.8, the INTERSECTION, UNION and INVERSE functions now perform as well as the older SUBSET functions and can be used instead in all cases.

So, for example, rather than use the somewhat convoluted:


  SUBSET3(field1, field2, field3, value1, value2, value3, 
          operator1, operator2, operator3)

you can now use the much simpler format:


  INTERSECTION(
    SUBSET(field1, value1, operator1),
    SUBSET(field2, value2, operator2),
    SUBSET(field3, value3, operator3)
  )

as well as using more complex logic such as:


  UNION(
    INVERSE(SUBSET(...)),
    INTERSECTION(
      SUBSET(...), SUBSET(...), ROWINDEXSUBSET(...)
    )
  )

Formula editing

Additionally, the formula editor now has improved UI performance and is easier to read, with contextual highlighting of relevant parts of a formula immediately surrounding the cursor position, allowing you to visualise the formula structure better.

Simplify and Reformat

Two new buttons have been added to the formula editor:

"Simplify" converts complex SUBSET2 to SUBSET5 variants into INTERSECTION-based formulae. In future it may also change use of other legacy functions into their recommended alternatives.

"Reformat" reformats all whitespace in your formula for readability.

Neither of these commands will affect the formula result, but "Simplify" may yield better performance in future.

SUBSET functions: Non-empty record count?

mohamed — Wed, 17 Oct 2012 05:34:17 -0400

I would like to calculate the number of weeks for each store where there are sales. In order to do this, I have to calculate the number of non-empty records for each store across all the weeks. I have 34 weeks. So the maximum of non empty records should be 34. I have used the following formula:

"SUBSET_NONEMPTYCOUNT([Sumofvalue], SUBSET2([Store Description],[Week]))"

Please see attached file and look at the formula "Week Count", the top value should return 34 but it returns 52. I am not sure where I am going wrong.

Please see the Pivot view, it shows the Weeks against all the stores where I see empty record, I would like to discard in the calculations.

Regards, Mohamed

Formulae: "Product" aggregation/measure function (2.8+)

steve — Tue, 09 Oct 2012 05:02:44 -0400

From tonight's build of 2.8 (2.8 b337+) you will be able to choose "Product" alongside "Sum", "Mean", "Range", etc. to apply to numeric fields when aggregating and as measures. You will need to click "Common Functions" in the function menu to reveal it. You will also be able to use SUBSET_PRODUCT in your formulae.

This is a somewhat specialist-use function and can result in very high, unusuable values. One typical use is investment analysis, where periodic returns (like 1.10 for 10% periodic return) need to be multiplied many times to obtain the return over much longer periods defined as subsets of rows using the SUBSET(X) clause of the the SUBSET_PRODUCT function.