16 Replies Latest reply: Oct 8, 2015 9:14 AM by Mohamed Ahid

# 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?

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

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

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

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"

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

This works fine in your sample:

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

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

you have to chose a date to get something .otherwise you can freeze a value on the date selector , or like mentionned below use a variable containing today date while you  haven't choosed a date .

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

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)

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

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

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

do I have to make define a variable?

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

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

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

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

Hi,

Try the following code:

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

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

Hi Darri,

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

Regards

Jyothish KC

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

The formula does not work properly.

I get error: "No data to display"