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

[#1]

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.

[disqr_rm]

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.

[#3]

Hi

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

Regards

Neil

[#4]

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 · Accepted solution]

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.

[#6]

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!

[#7]

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]

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