Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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
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)
=count({$< [Activity Type] = {'1'}, Date={'>=$(=MonthStart(vMaxDate, -11))<=$(=Today())'} >}UnitID)/count(UnitID)
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"
Hi,
Try the following code:
count({$< [Activity Type] = {'1'}, Date={'>=$(=MonthStart(max(date), -11))<=$(=monthstart(Today())'} >}UnitID)/count(UnitID)
do I have to make define a variable?
I can't use max(date) in the set analysis?
This works fine in your sample:
=count({$< [Activity Type] = {'1'}, Date={'>=$(=MonthStart(vMaxDate, -11))<=$(=vMaxDate)'} >}UnitID)/count(UnitID)
>>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.