Formula Use Cases

This page contains examples of the particular issues regarding reporting in BellaDati. The solutions is described and the structure backup and data included for your needs.

Counting frequency

The goal is to display the frequencies of visits based on visit counts in individual countries. The we would like to see these frequencies by cathegories and also regions.
Visit frequencies are defined as formula indicators described below.

Download: Demo structure & data.

Frequencies

int a = 0
membersSum('L_COUNTRY') {
  int v = value('L_COUNTRY_COUNT')
  if (v == 2) { // For 2 visits per interval
    a++
  }
}
return a

Frequencies by cathegories

int a = 0
membersSum('[L_CATHEGORY][L_COUNTRY]') { // Cathegory added
  int v = value('L_COUNTRY_COUNT')
  if (v == 2) { // For 2 visits per interval
    a++
  }
}
return a

Frequencies by cathegories and regions

int a = 0
membersSum('[L_REGION][L_CATHEGORY][L_COUNTRY]') { // Region and cathegory added
  int v = value('L_COUNTRY_COUNT')
  if (v == 2) { // For 2 visits per interval
    a ++
  }
}
return a

Please note, frequencies will not be displayed correctly when adding another drill down on the right of the table in current version. Use filter instead. This can be also combined with report variables.

Getting the latest available value

The following expression is suitable for month date granularity:

def result
def m = dateMonth()
def y = dateYear()
def days = daysInMonth()
(1..days).each() { i ->
   def when = i.toString()+'.'+m+'.'+y
   def value = dateAt(when, 'DAY') { M_INDICATOR_CODE }
   if (value != Double.NaN) {
     result = value
   }
}
return result

Average cumulated payments during financial year

The result will be the cumulated value of the indicator (eg. payments) divided by number of months from the beginning of the financial year. This tells you how the total payments average trend changes.
Note: Changes might be necessary when adding drill-down.

def y = dateYear();
def m = dateMonth();
def result;
def int monthTotal;

monthTotal = m + ((y-2011)*12) - 6; // eg. FY starts in July 2011
result = cumulateFromDate('2011-07-01','M_INDICATOR') / monthTotal;

return result;

Page tree