en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Jimmy  
#1 Posted : Tuesday, May 24, 2016 1:07:57 PM(UTC)
Jimmy
Rank: Member

Groups: extranet\Forum
Joined: 5/24/2016(UTC)
Posts: 5

Hello
Firstly, I hope I've written in the right forum. I've been using Targit for a while but nothing advanced like calculations with formulas.
Therefore, I'm not 100 pct. sure what it means when you refer to "d1", "m1", "c1" etc.

The thing I want is rather simple, but I don't know if the solution is simple at all. I'd like to insert a row of different numbers. Currently, I've inserted 337 in 5th row but I'd like it to be variable between each week number due to seasonal adjustments, i.e. 300 in week 17, 353 in week 18, 315 in week 19 etc.

Is there any way to do this? So far I've only been able to use the syntax:

Code:
337


To insert a whole row of 337s.

UserPostedImage

I hope you understand, what I'm asking and I hope you can help.

Thanks in advance,
Jimmy
Páll Bjarkason  
#2 Posted : Wednesday, May 25, 2016 7:58:25 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

Hi Jimmy

It is possible to write a calculation utilizing the, if else statement, but when you are on the week level there are allot of if/else's keep track of...

Here is an example on the syntax on demo data.
If you time dimension definition is Year-Week then your syntax would be something like

if allcount(d1:@"[2016].[17]", d1, m1) = allcount(d1:0, d1, m1) then 337 else if allcount(d1:@"[2016].[18]", d1, m1) = allcount(d1:0, d1, m1) then 338 else 339

UserPostedImage


To check the syntax you can start by getting the desired result from the instructional calculations
d1:0 allcount(d1:0, d1, m1)
@ sum(@"[2012].[Q1]", d1, m1)
Best regards

Páll, CTS
Jimmy  
#3 Posted : Wednesday, May 25, 2016 9:17:00 AM(UTC)
Jimmy
Rank: Member

Groups: extranet\Forum
Joined: 5/24/2016(UTC)
Posts: 5

Thank you very much Páll. It works flawlessly. I just need to write out the entire syntax for all the weeks now.
I'm worried that there's a limit to number of characters in Targits custom calculation field but we'll see about that.

Is it possible to modify the syntax so it works with dynamic time criterias? If it only shows the previous four weeks, e.g. week 17, 18, 19 and 20, then the syntax unfortunately doesn't work.

Thanks again,
Jimmy
Páll Bjarkason  
#4 Posted : Wednesday, May 25, 2016 10:41:03 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

If you use the dynamic criteria to show week 17-20 it is still possible to have a calculation ranging from e.g. week 10-30.
Then you don't have to update the calculation every week.
The crosstab will of course only show the dynamic selected values. I.E. week 17-20.
Best regards

Páll, CTS
Jimmy  
#5 Posted : Wednesday, May 25, 2016 12:13:12 PM(UTC)
Jimmy
Rank: Member

Groups: extranet\Forum
Joined: 5/24/2016(UTC)
Posts: 5

Thanks for your response, however I doesn't work for me. Did I miss anything?
My current syntax is:

Code:
if allcount(d1:@"[2016].[1]", d1, m1) = allcount(d1:0, d1, m1) then 300
else if allcount(d1:@"[2016].[2]", d1, m1) = allcount(d1:0, d1, m1) then 350
else if allcount(d1:@"[2016].[3]", d1, m1) = allcount(d1:0, d1, m1) then 336
else if allcount(d1:@"[2016].[4]", d1, m1) = allcount(d1:0, d1, m1) then 303
else if allcount(d1:@"[2016].[5]", d1, m1) = allcount(d1:0, d1, m1) then 380
else if allcount(d1:@"[2016].[6]", d1, m1) = allcount(d1:0, d1, m1) then 353

<snip>

else if allcount(d1:@"[2016].[52]", d1, m1) = allcount(d1:0, d1, m1) then 309 else 0


When I have no time criteria, I get this picture:

UserPostedImage

But as soon I set on dynamic time criteria (between this week-4 and this week-1) I get following:

UserPostedImage

I tried messing around with d-1 instead of d1 but I really can't get it to work. :(

Jimmy
Ole  
#6 Posted : Wednesday, May 25, 2016 8:20:08 PM(UTC)
Ole
Rank: Administration

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

Hi Jimmy,

You should try and introduce the "undefined value" in your calculation.

Example: allcount(d1:@"[2016].[1]", d1, m1, 0). Notice the "0" at the end of the syntax. In cases where [2016].[1] is not part of your data set (due to criteria settings) you would normally see the "Undefined" text. However, you can set your own "Undefined" value - in my example simply a "0". It can be a different value of course, or even an entirely different calculation.

Bets regards,
Ole
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
Jimmy  
#7 Posted : Thursday, June 2, 2016 9:03:21 AM(UTC)
Jimmy
Rank: Member

Groups: extranet\Forum
Joined: 5/24/2016(UTC)
Posts: 5

Hello Ole
Thanks for your response, I've been off work for a week which is why I haven't responded, however my issue still stands.
Your syntax to assign other values to "undefined" is nice but it doesn't seem to work for me. Here's a screenshot:

UserPostedImage
Ole  
#8 Posted : Monday, June 6, 2016 5:48:01 PM(UTC)
Ole
Rank: Administration

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

Please, upload an example of the syntax after you modified it to handle "undefined" states.
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
Jimmy  
#9 Posted : Tuesday, June 7, 2016 7:40:33 AM(UTC)
Jimmy
Rank: Member

Groups: extranet\Forum
Joined: 5/24/2016(UTC)
Posts: 5

Sorry! Should have uploaded the syntax in my last reply. As I stated in the first post, I'm not very experienced in Targit syntax, so I may have done something wrong. Anyways here's the code for the row of manually inserted entries:

Code:
if allcount(d1:@"[2016].[1]", d1, m1, 0) = allcount(d1:0, d1, m1, 0) then 300
else if allcount(d1:@"[2016].[2]", d1, m1, 0) = allcount(d1:0, d1, m1, 0) then 350
else if allcount(d1:@"[2016].[3]", d1, m1, 0) = allcount(d1:0, d1, m1, 0) then 336
else if allcount(d1:@"[2016].[4]", d1, m1, 0) = allcount(d1:0, d1, m1, 0) then 303
<snip>
else if allcount(d1:@"[2016].[51]", d1, m1, 0) = allcount(d1:0, d1, m1, 0) then 368
else if allcount(d1:@"[2016].[52]", d1, m1, 0) = allcount(d1:0, d1, m1, 0) then 309 else 0


Which produces the following output when I have a time criterium active.

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