en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Páll Bjarkason  
#1 Posted : Wednesday, May 29, 2013 1:03:20 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Hi

I have added a calculated column ‘as a new measure’ for each month. Using this code:

if allcount(d1:0, d1, m1) = allcount(d1:@"[Januar]", d1, m1) then 31 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[Februar]", d1, m1) then 28 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[Marts]", d1, m1) then 31 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[April]", d1, m1) then 30 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[Maj]", d1, m1) then 31 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[Juni]", d1, m1) then 30 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[Juli]", d1, m1) then 31 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[August]", d1, m1) then 31 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[September]", d1, m1) then 30 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[Oktober]", d1, m1) then 31 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[November]", d1, m1) then 30 else
if allcount(d1:0, d1, m1) = allcount(d1:@"[December]", d1, m1) then 31 else 1000

Via the criteria I can choose which months to look at.

When I look at a whole year then I get the right number of days in all months.
When I choose one month, then January it’s a success. But all other months fail returning Undefined

Can someone please explain what‘s wrong?
Best regards

Páll, CTS
Lotte Bjørn Jensen  
#2 Posted : Thursday, May 30, 2013 9:53:46 AM(UTC)
Lotte Bjørn Jensen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/8/2011(UTC)
Posts: 68

Hi Páll

I've experienced the same when dimension value doesn't exist.

Couldn't you use a calculation to count the number of days in the month?

Best regards

Lotte Bjørn
Ole  
#3 Posted : Friday, May 31, 2013 3:28:00 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 325

Hi Páll,

I have come up with a "somewhat" solution - not the best, I admit, but maybe it can inspire others to try and make it better.

The problem is that it goes into "Undefined" state when doing a range calculation on a dimension member that does not exist. My solution is to do an up front check to see if we will hit a undefined state, and if positive, then avoid the range calculation on the undefined state.

The downside is that it will only be able to show the correct days-of-month number for one month at a time. I.e. when you select just one month from the criteria bar, then this month month will show the correct number - if you have multiple months, only the first month will show the correct number (the others will zero).

if sum(@"[Jan]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Jan]", d1, m1) then 31 else 0) else
if sum(@"[Feb]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Feb]", d1, m1) then 28 else 0) else
if sum(@"[Mar]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Mar]", d1, m1) then 31 else 0) else
if sum(@"[Apr]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Apr]", d1, m1) then 30 else 0) else
if sum(@"[May]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[May]", d1, m1) then 31 else 0) else
if sum(@"[Jun]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Jun]", d1, m1) then 30 else 0) else
if sum(@"[Jul]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Jul]", d1, m1) then 31 else 0) else
if sum(@"[Aug]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Aug]", d1, m1) then 31 else 0) else
if sum(@"[Sep]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Sep]", d1, m1) then 30 else 0) else
if sum(@"[Oct]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Oct]", d1, m1) then 31 else 0) else
if sum(@"[Nov]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Nov]", d1, m1) then 30 else 0) else
if sum(@"[Dec]", 0, m1, 999) <> 999 then (if allcount(d1:0, d1, m1) = allcount(d1:@"[Dec]", d1, m1) then 31 else 0) else 0

Best regards,
Ole

Edited by user Friday, May 31, 2013 3:30:36 PM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Søren Damgaard Jensen  
#4 Posted : Tuesday, June 4, 2013 2:27:33 PM(UTC)
Søren Damgaard Jensen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/1/2011(UTC)
Posts: 5

I would build this kind of logic into my cube instead of a calculation in TARGIT. This could also handle the exceptions where February has 29 days.
Best Regards,

Søren Damgaard Jensen
Senior BI Consultant @ NORRIQ

http://www.norriq.dk
Aristides Vassis  
#5 Posted : Sunday, June 9, 2013 10:13:29 PM(UTC)
Aristides Vassis
Rank: Member

Groups: extranet\Forum
Joined: 5/8/2013(UTC)
Posts: 6

Hello Páll,

I would suggest creating a count measure on the Time table you use for your solution, which would cover most of the uses of finding how many days a month has. On the odd chance you need to show the days in a month for each day of the month then I would go with a new column in the Time table (daysinmonth).

Another helpful trick is to use an autoincrement column in your Time table and have a min and a max measure from this. The difference between the min and the max will show the number of days in the interval selected (like datediff does in sql).
Best regards,

Aristides Vassis
BinaryTree / TARGIT Greece

www.binarytree.gr

UserPostedImage
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error