Conditional dimension

[#1]

Hi,

I have a bar chart which shows active contract for the 6 previous months. The thing is there are fictitious end dates for some of the active contracts causing one of the months to be for the year 2099. These contracts still need to be counted, but should be grouped with the current month instead of June 2099.

This is what I have in my load script to create my dimension:

AddMonths(MonthStart(StartDate),iterno()-1) as ContractMonth

While MonthStart(EndDate) >= AddMonths(MonthStart(StartDate),iterno()-1);

And this is my expression:

Count({<Company = {'24'}, Status = {A}>} CustomerNo)

Any help is welcome!

[#2]

I personally would handle this in the load script vs in a calculated dimension:

replace EndDate with:

if(EndDate > now() ,

      month(now()) & ' ' & year(now())  //or Whatever logice you use for current month

     ,EndDate) as EndDate

[CELAMBARASAN]

Change the while loop to

While MonthStart(Min(EndDate,Today())) >= AddMonths(MonthStart(StartDate),iterno()-1);

Hope it helps

[#4]

Hi Celambarasan,

Thanks for replying!

This causes the script to fail and I'm promted to reload old dato (yes/no). Any suggestions?

[#5]

Hi klangley,

Thanks for replying!

The above is in my load script, and I'm then adding the ContractMonth as a dimension in the bar chart.

Are you suggesting using EndDate as the dimension with the logic you posted? That doesn't seem to to the trick unfortunately.

[CELAMBARASAN · Accepted solution]

Sorry its my mistake

Check with this

While MonthStart(RangeMin(EndDate,Today())) >= AddMonths(MonthStart(StartDate),iterno()-1);

[#7]

Thanks!

That solved the issue!

[natebrunner]

Just wanted to say thanks for this! Works like charm in a Type 2 table I have where End-Dates are set to '31-DEC-9999'.