topic Re: Conditional dimension in QlikView

Conditional dimension

Mon, 22 Oct 2012 13:04:59 GMT

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!

Re: Conditional dimension

Mon, 22 Oct 2012 13:18:18 GMT

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

CELAMBARASAN — Mon, 22 Oct 2012 13:25:30 GMT

Change the while loop to

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

Hope it helps

Re: Conditional dimension

Tue, 23 Oct 2012 07:53:29 GMT

Hi Celambarasan,

Thanks for replying!

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

Re: Conditional dimension

Tue, 23 Oct 2012 07:58:50 GMT

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

CELAMBARASAN — Tue, 23 Oct 2012 12:55:57 GMT

Sorry its my mistake

Check with this

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

Re: Conditional dimension

Tue, 23 Oct 2012 13:24:21 GMT

Thanks!

That solved the issue!

Re: Conditional dimension

natebrunner — Wed, 10 Dec 2014 19:06:40 GMT

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