Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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.

View solution in original post

7 Replies
Highlighted
Partner
Partner

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.

Highlighted
Not applicable

Hi

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

Regards

Neil

Highlighted
Not applicable

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?

Highlighted
MVP
MVP

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.

View solution in original post

Highlighted
Not applicable

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!

Highlighted
Not applicable

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!



Highlighted
Creator
Creator

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