Point in time question

[alexis]

In the attached application I am showing a subset of my application to illustrate the problem that I am facing.

Specifically, I calculate a value called "Churn" (the formula is stored as a variable) which uses,among other things,the, lower and upper ends of the date range selected to calculate.(i.e. we need to know how many subscribers we had at the beginning of the period and how many at the end of the period.

So far so good and the attached application works fine for any period selected - say a year, a quarter or a month!

The problem that I am facing is that if I select a period of a year or a quarter, I need to show the "churn" value for each month.

Any pointers/help would be very much appreciated.

Alexis

[whiteline]

Hi.

You have to remove the equal sings from variables in that case, and possible add some brackets to make the calculations right.

Otherwise the value is substituted instead of the formula (that what you want).

There are some good posts about dollar-sign expansion and variables.

[alexis]

Thanks Whiteline,

Can you see something that can be simply changed to achieve the goal - I am a little confused about the suggestion 😞

Thanks

Alexis

[whiteline]

Hi.

You've used $-sign expansion of variables. There are two different options.

One when the result of an expression is assigned to variable (like you did), the expression starts from '=' sign and is calculated once (for each selection change). So when you write $(var) just the result is substituted which was calculated before regardless of dimension value.

Another option is to assign a string to variable that represents an expression. Omit the '=' sign in this case, so when you write $(var) the string with the expression is substituted. After all expansions you get a complex expression which is used for calculations. Now it's calculated for each dimension value.

The goal is not obvious for me. You have activity date limitations, should it be dynamic for the month chart ?

[alexis]

Hi

That was a very helpful post.

Removing the "=" sign has partially solved my problem but not completely 😞

In my application, I calculate a ratio which I call "Churn Ratio" which is calculated as:

Churn Ratio = A / ((B+C) / 2 )  as a percentage

where:

A = subscribers churned (lost) during period

B = total subscribers at the beginning of the period

C = total subscribers at the end of the period

A simplistic version of the formula for (A) which I store in a variable, is:

=sum({1<ActivityDateNo={">=$(vMinActivity)<=$(vMaxActivity)"} CustomerRecNo))

where ActivityDateNo is the dates in numeric representation (e.g. 1/1/201 is 41640)

and the variables vMinActivity and vMaxAvtivity are:

vMinActivity = min(ActivityDateNo)

vMinActivity = max(ActivityDateNo)

SINCE REMOVING THE "=" sign to the definition (as you suggested) THIS PART WORKS FINE (see new version of the application attached) - it works with a dimension month, week etc!!!

A Simplistic version of the formula for (B) which I also store in a variable is:

=sum({1<ActivityDateNo={"<$(vMinActivity)"} CustomerRecNo))

and (C)

= sum({1<ActivityDateNo={"<$(vMaxActivity)"} CustomerRecNo))

The problem with both (B) and (C) is:

- They both needed "full accumulation" in order to show the correct numbers

- "B" only shows periods less than the selected period

- "C" show figures for all periods up to and including  maximu

If you see the application you will understand the behaviour I am referring to.

Any assistance would be greatly received..

Alexis

[whiteline]

Hi.

Common way to solve dynamic accumulation is to create 'accumulation calendar' or 'as of date calendar'.

This means that you create table with fields ActivityDate and AsOfActivityDate where for every value of AsOfActivityDate you have all the dates before  ActivityDate.

Now you can use AsOfActivityDate to modify the constraints and to use as chart dimension.