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

[Anonymous]

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?

[mohamed_ahid]

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

[jonathandienst · Accepted solution]

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)

[jonathandienst]

[jonathandienst]

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

[Anonymous]

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"

[#7]

Hi,

Try the following code:

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

[Anonymous]

do I have to make define a variable?

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

[jonathandienst]

This works fine in your sample:

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

[jonathandienst]

>>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.