Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
johnw
Champion III
Champion III

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
disqr_rm
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

Hi

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

Regards

Neil

Not applicable
Author

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?

johnw
Champion III
Champion III

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.

Not applicable
Author

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!

Not applicable
Author

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!



shahamel
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