Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How to see 12 months back in a chart with Max(date)

Hi, QV

I have a dataset that looks like that

 Date UnitID Activity Type 1.12.2015 4 1 1.12.2015 5 1 1.12.2015 6 1 1.12.2015 7 2 2.10.2015 8 2 2.10.2015 9 1 2.10.2015 10 2 2.10.2015 11 1

I am want to know how many UnitID have activity type = 1

So I have used this formula

=count({\$< [Activity Type] = {'1'} >}UnitID)/count(UnitID)

Then I wanted to see 12 month back. So I used this formula:

=count({\$< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(Today(), -11))<=\$(=Today())'}  >}UnitID)/count(UnitID)

But the problem with this is when I click on date in 2014 I get no data to display which is not strange.

But I how can I see ALWAYS 12 months back. So when I select date in 2014 that is my max(date) and see 12 months back from that date.

I was wondering how can I use Max(date) in the formula?

1 Solution

Accepted Solutions
Partner - Champion III

Define a variable, lets call it vMaxDate as =Max(Date) (not the = sign is part of the definition), or define it in the load script:

Set vMaxDate = '=max(Date)';

Now use the variable in place of Today()

=count({\$< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(vMaxDate, -11))<=\$(=Today())'}  >}UnitID)/count(UnitID)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
12 Replies
Partner - Specialist

HI

try :

count({< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(Date, -11))<=\$(=Date)'}  >}UnitID)/count({<Date={'>=\$(=MonthStart(Date, -11))<=\$(=Date)'}  >}UnitID)

is it what you want ?

regards

Partner - Champion III

Define a variable, lets call it vMaxDate as =Max(Date) (not the = sign is part of the definition), or define it in the load script:

Set vMaxDate = '=max(Date)';

Now use the variable in place of Today()

=count({\$< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(vMaxDate, -11))<=\$(=Today())'}  >}UnitID)/count(UnitID)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner - Champion III

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner - Champion III

=count({\$< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(vMaxDate, -11))<=\$(=Today())'}  >}UnitID)/count(UnitID)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

yes, I want something like that.

But it does not work when I put the formula in the set analysis.

I get an error: "No data to display"

Not applicable
Author

Hi,

Try the following code:

count({\$< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(max(date), -11))<=\$(=monthstart(Today())'}  >}UnitID)/count(UnitID)

Anonymous
Not applicable
Author

do I have to make define a variable?

I can't use max(date) in the set analysis?

Partner - Champion III

This works fine in your sample:

=count({\$< [Activity Type] = {'1'}, Date={'>=\$(=MonthStart(vMaxDate, -11))<=\$(=vMaxDate)'} >}UnitID)/count(UnitID)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner - Champion III

>>do I have to make define a variable?

>>I can't use max(date) in the set analysis?

You could do that. The expression just gets a little messy.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser