Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Sorry its my mistake
Check with this
While MonthStart(RangeMin(EndDate,Today())) >= AddMonths(MonthStart(StartDate),iterno()-1);
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
Change the while loop to
While MonthStart(Min(EndDate,Today())) >= AddMonths(MonthStart(StartDate),iterno()-1);
Hope it helps
Hi Celambarasan,
Thanks for replying!
This causes the script to fail and I'm promted to reload old dato (yes/no). Any suggestions?
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.
Sorry its my mistake
Check with this
While MonthStart(RangeMin(EndDate,Today())) >= AddMonths(MonthStart(StartDate),iterno()-1);
Thanks!
That solved the issue!
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'.