Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Conditional dimension

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!

1 Solution

Accepted Solutions

Re: Conditional dimension

Sorry its my mistake

Check with this

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

7 Replies
Not applicable

Re: Conditional dimension

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

Re: Conditional dimension

Change the while loop to

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

Hope it helps

Not applicable

Re: Conditional dimension

Hi Celambarasan,

Thanks for replying!

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

Not applicable

Re: Conditional dimension

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.

Re: Conditional dimension

Sorry its my mistake

Check with this

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

Not applicable

Re: Conditional dimension

Thanks!

That solved the issue!

natebrunner
New Contributor II

Re: Conditional dimension

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

Community Browser