topic Re: Split year into six months and show all values for the dimension within half-year in QlikView

Split year into six months and show all values for the dimension within half-year

Wed, 17 Feb 2010 20:24:46 GMT

Hi,

I want to show yearly data split into two half-years. I put this expression as a calculated dimension:

=if(Month='jul' or Month ='aug' or Month = 'sep' or Month = 'oct' or Month = 'nov' or Month = 'dec', Month)

and it works fine.

But I always want the month dimension to show even if there are no values in the expressions. Problem is that when I check suppress when value is null and check show all values on the dimension and uncheck Show zero values in presentaion it doesn't work.

I know it works if I don't use the calculated dimension but then I don't get the split into six months :).

Any ideas? Id rather use some form of split on the dimension than the expression, otherwise I have to do over all the expressions used.

Split year into six months and show all values for the dimension within half-year

disqr_rm — Wed, 17 Feb 2010 20:34:05 GMT

I would rather create a field in the data load where you have your dates (calander?) something like:

LOAD if(month(date) < 7, 'First Half', 'Second Half') as HalfYear;
LOAD
....
....
Date,
Month,
....
FROM....

Then use this HalfYear as dimension. I think it would be much cleaner and performant.

Split year into six months and show all values for the dimension within half-year

Wed, 17 Feb 2010 21:12:16 GMT

I would agree with Rakesh this is definitely the best way to solve your problem

Regards

Neil

Split year into six months and show all values for the dimension within half-year

Thu, 18 Feb 2010 00:49:27 GMT

Thanks for the suggestion but I might have been a bit unclear 🙂

I want to show the values month for month, not on a total for the first six months ie HalfYear just that i want it in two different tables.

Table 1

jan-jun

Table2:

jul-dec

This is because I want to optimize the usage of space on the screen and also in printed pdf reports. A full year goes off screen.

Or am I missing something in the suggested solution?

Split year into six months and show all values for the dimension within half-year

johnw — Thu, 18 Feb 2010 00:58:30 GMT

Perhaps this, then, again in your script:

if(Month<7,Month) as FirstHalfMonth,
if(Month>6,Month) as SecondHalfMonth,

Then just use FirstHalfMonth in your first table, and SecondHalfMonth in your second table. You then don't need to check the suppress when null, which may help you out some trying to get what you want. It should also be much more efficient than a calculated dimension.

Split year into six months and show all values for the dimension within half-year

Thu, 18 Feb 2010 01:12:52 GMT

I just solved it, rakesh put me in the right direction:

if(month(date) < 7, month(date)) as HalfYear1,

if(month(date) > 6, month(date)) as HalfYear2,

Thanks!

Split year into six months and show all values for the dimension within half-year

Thu, 18 Feb 2010 01:22:11 GMT

Thanks John,

didn't see your post before replying. As you said, works a lot better than a calculated dimension.

I do however have to put in supress when null and show all values, otherwise I get a column at the end with no dimension value, ie an empty month.

Thanks all!

Re: Split year into six months and show all values for the dimension within half-year

shahamel — Mon, 02 Jul 2012 09:01:22 GMT

I think it is also possible to use that format within a expression. It gives the possibility to use both half of a year within one table