Tagged with functions-and-formulae - Visokio Forums

SearchRegex Formula not working

JohnMR — Mon, 18 Sep 2017 06:30:00 -0400

Hi Omniscopers,

I'm trying to use Searchregex formula to pull

=searchregex("^.{3}[A-Za-z0-9 ]+\K.",[Campaing],1)

Searchregex usually works but not when I add the "\" in \K I get an error: Error with formula: Invalid text """ - an open bracket, literal number, reference or function was expected.

When I remove the slash the formula works but I need that slash in it. Why is this not working?
Thanks,
John.

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?

Functions : Excel Equivalent to TODAY()

mttwnxn — Mon, 20 Feb 2017 06:34:24 -0500

Hi - is there any equivalent to the TODAY() function in excel?

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

Data Validation: Adding Leading Zeros

aa1708 — Wed, 23 Nov 2016 10:45:42 -0500

Hi there, I have a column with hundreds of phone numbers but some records don't have leading zeros. Is there a way to construct a formulae to add zeros to the ones without?

Counting sub-strings: Using Subset

aa1708 — Thu, 17 Nov 2016 07:17:36 -0500

Hi there, I have a table with PhoneNum attribute in it. In the PhoneNum field, I have numbers starting with 0845, 0843, 0871 and so on. How can I count the total numbers starting with 0845, 0843 and so on?

Aggregation: Finding the Row count

medine — Thu, 08 Sep 2016 03:26:00 -0400

Hello,
I started to use Visokio to export my reports, but I can not calculate row count when I aggregate data.

Could you assist me?

Switching measures by using Variables: Ordering of Field Options

Phillipa — Thu, 09 Jun 2016 08:56:06 -0400

Hi,
I have created a variable (Analyse by:) to allow users to select a field to put on the Y axis of a pivot table e.g. age, fitness etc

A formula field (Stack by) then links their selection to the Y axis. It is working well with one slight snag - the ordering of the options in the selected field is coming out alphabetically and not as in the original field. It does not appear possible to change this.

Does anyone have workaround to this problem?

Thanks. Phillipa

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!

Ranking top x items - aggregated values

bernardpi — Mon, 18 Apr 2016 11:43:53 -0400

Hi there. I have a content view where I have written Javascript to run through a list of items and aggregate on a field in order to be able to rank the items. In context, I'm running through a list of employee transactions (not-unique) and ranking them by total amount spent. This involves performing a subset sum on all employee transactions and then performing a sort and reverse to get my sorted list which I then use for downstream calculations.

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,

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/

Demo: Dynamic calculation of investments with different maturity dates

paola — Tue, 17 Nov 2015 07:16:46 -0500

Simple demo attached, illustrating the use of DATEDIFF formula in the scenario where investments/deposits have different maturity date.
First step is calculation of number of days between today and the investment maturity date:

DATEDIFF(NOW, [Date], "days")

By using NOW function, this calculation will keep the report up-to-date, always returning the latest breakdown.
It is also possible to 'fix' the date and use DATEVALUE("dd/MM/yyyy") instead of NOW, e.g. DATEDIFF(DATEVALUE("01/10/2015"), [Date], "days") .
Second step is to create irregular time intervals, that will 'bucket' the records and be used for sum calculation.


IF(
[Difference days]<1,"matured",
[Difference days]<2,"1 day",
[Difference days]<8,"1 week",
[Difference days]=<31,"1 month",
[Difference days]<92,"3 months",
[Difference days]<184,"6 months",
[Difference days]<366,"1 year",
[Difference days]<1096,"3 years",
[Difference days]<1827,"5 years",
[Difference days]<2557,"7 years",
[Difference days]=<3653,"10 years",
[Difference days]>3653,"over 10 years",
null)

Aggregation and summing of investment amounts for each period is done in the Bar/Line view.

Formulae: Replacing substring in a text field

Phillipa — Tue, 03 Nov 2015 11:42:47 -0500

Hello,

I have a text field with different strings.
I am trying to replace the first 2 characters of each string.

Specifically I want to replace "PT" with "m1"; "m1" with "m2"; "m2" with "m3" etc.

I have seen that there is a REPLACE and REPLACEREGEX function, which I would be interested in using,
but can't seem to get either of them to work correctly. Could someone help?

Thanks
Phillipa

Idea: Formulae: Text Fill Down?

hgross — Wed, 15 May 2013 14:07:15 -0400

Is there a way to fill blank rows from data above in the same column? It might be easier to see the attached screenshot.

I essentially want all of the blank rows to be filled in with the text above until it reaches the next text string.

Thanks!

Formulae: Dynamic Field Names?

kmccready1 — Tue, 25 Aug 2015 05:48:08 -0400

Hi, I was wondering if anyone knows if it's possible to add a dynamic field heading to a table of data? for instance if I wanted to find out the total value of deposits at the end of the week then I would want the heading to be "Actual Balance ". Then when running the report a week later I would want the heading to be "Actual Balance " etc?

Formula: Scaling values in Field Organiser...or separate Formula field?

dpawley — Mon, 08 Jun 2015 11:18:41 -0400

In DataManager, where you have a Field Organiser block, if you have a data field that is a decimal number and you would like to multiple this by 100 or 200, to show in the report, can I just add a scaling formula in the field or do I need to create a whole new formula field to calculate this.

I want to just convert the number from Hectolitres to Litres.

Cheers

Danny

Date/Time: DateToText Function, using "yyyy" and not "YYYY"

benjamin — Wed, 25 Mar 2015 14:08:27 -0400

Hi,
Using the DatetoText function results in errors when you use dates near the end of the year (anywhere from the the 29th-31st of Dec)
Example :
DATETOTEXT(date(2013,12,31),"YYYY")

Thanks, Ben

Functions: Text String Length?

alexandervolk — Tue, 17 Mar 2015 11:48:30 -0400

How can I measure the length of a string in a given field? I assume there must be a function for it.

Many thanks!
Alex

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)

Formulae: Weighted Average in Content View & stacked Bar Charts?

DominicChan — Fri, 07 Nov 2014 05:06:09 -0500

Hi, in the Content View when using "function" to display calculated figures, besides using formula, is there a way to display a Weighted Average? The Mean function there doesn't seem to offer a "weight" setting.

Also, when doing a weighted average in the Bar View, after stacking the bars, the figure displayed at the top of the bars are the sum of the stacking buckets. How can I make the figures on top of the bars to display an overall weighted average?

Thanks
Dom

Formulae: bracketing values using IF logic?

kantia8 — Wed, 05 Nov 2014 17:36:21 -0500

I am trying to do an analysis where I want to rename variables if they are between at set of numbers
i.e.: if the value of [Field1] is >10 and <20, then the value is changed to something like "Page2" in the [Formula Field1]

I know how to use the operators "=", ">" and "<", but I don't know how to do in between. I looked at the bucket function, but that seems to only work for dates and not other field types?

Formulae: Dynamic/evaluated reference to a formula in a field?

fcavalieri — Wed, 29 Oct 2014 09:18:23 -0400

Hi,
I'd like to know if it is possible, in a formula field i.e. [Cost_Formula],
to refer to and run a specified formula contained in another text field i.e. [Formula].

I attach a file for example (the exact result I'd like to obtain is the same of Cost field).

Thanks, Franci

Unstructured Data transformation: Reading in Transactional data

freddiewalker — Tue, 07 Oct 2014 07:41:48 -0400

Hi there,
I have some transactional data I would like to read into Omniscope if possible.. If not, any ideas of other programs/software I could potentially use would be appreciated.
I have attached an excel file with a few quick examples of the data (and its breakdown). Ideally I would like to read in each separate transaction into an amount of records that corresponds to how many products there are in that transaction. So for each product I would have date/time/payment method/units/value/multibuy information etc...

Would this be possible on Omniscope?
Many thanks.

Formulae: Calculating Week Start - Week Ending dates?

Panos — Wed, 17 Sep 2014 13:48:34 -0400

Hi
Is there any formula so I can get the "week start" or "week end" for each week number?
Regards,
Panos

Formulae: Calculating 12-month Moving Average from daily data?

nitiwan — Mon, 18 Aug 2014 04:16:06 -0400

If we have volume (orders) everyday about 20-30 orders per day, what is the best way to calculate moving average for 12 months? Are there any automated function or we have to write a formula?

Thanks so much...Tai

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

Formulae: RANK Syntax-Ranking on aggregated values, etc

paola — Fri, 21 Feb 2014 14:27:18 -0500

Please see the demo file covering number of scenarios showing how to Rank all values or aggregated values; or rank on more than one field.
Some of the formulas used:
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.

Formulae: Adding Time to Dates?

Valentina — Wed, 18 Jun 2014 03:49:57 -0400

Hi,

I have a Date column and a column showing the number of days ahead. Eventually I would like to get the Date ahead i.e. if the Date today is 10/06/2014 then in two days it will be 12/06/2014. Please see the example below.

[Date] [ DaystoAdd] [Date ahead]
10/06/2014 2 12/06/2014
04/06/2014 5 09/06/2014
30/05/2014 6 05/06/2014

Any ideas how to achieve this?

Thanks in advance