extended syntax - generating ytd and mtd columns using

system · October 25, 1998, 10:53pm

formulas

In a message dated 98-10-25 17:38:48 EST, you write:

I’ve tried to add a column which checks the process date and only show
the commission amount if the process date month is the same as the current date month. It looks like:
=(If(=) Then Else (0)).

This gives me an #ERR. If I add the process date to the report as a column then the formula works.

Any object that is used in a calculation must be part of the block. That’s why you get an error if you don’t have it, and why the formula works when it is there. The trick is to have the object available but not displayed.

To do that, first add the date to the block so that your calculation works. Next, click on the date data. From the Format menu select Table (or Crosstab, if you are using that type of block). From the screen that comes up, select the Pivot tab on the top. There you will see a list of all of the variables currently used in the block.

Here the process gets slightly different depending on whether you are using 4.0.x or 4.1.x. If you are using 4.1.x, click on the date variable and click on the Hide button that you will see on the right side of the window. If you are using 4.0.x, then right-click on the variable to hide it.

Hiding the variable will ensure that it is still part of the block, but is not displayed. That should fix your problem.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · October 26, 1998, 2:02pm

formulas

Thanks for the response,

On Sun, 25 Oct 1998 17:53:58 EST, DRathbun@AOL.COM wrote:

To do that, first add the date to the block so that your calculation works. Next, click on the date data. From the Format menu select Table (or Crosstab, if you are using that type of block). From the screen that comes up, select the Pivot tab on the top. There you will see a list of all of the variables currently used in the block.

Hiding the variable will ensure that it is still part of the block, but is not displayed. That should fix your problem.

I had tried this method and the “if” statement did work properly. The problem is that I now have rows for each date. What I’d like to get is a row for each producer within an office with ytd and mtd values. I was able to get this by running 2 queries; one with all the rows(ytd) and a second with current month values and putting together a report with values from both queries.

Is there any way to access data that has been brought back through a query, but not in the block. I’ve seen some samples using extended syntax that use dimension info that’s not in the block, but used to aggregate info: =Min( In (,). is not shown in the block. I haven’t seem samples using the “if” statement. Any other suggestions??

Thanks again,
Giorgio

Listserv Archives (BOB member since 2002-06-25)

system · October 26, 1998, 2:57pm

formulas

Giorgio,

In order for you to do IF…Then… Else comparisons on an object, the object needs to be in the body of the report. Your solution of adding it as a column was the correct resolution, however it seems that you don’t want the value to appear. What you need to do is select: Format\Table

From this, choose PIVOT.
Select the and then HIDE it by clicking on [HIDE].

Most Likely in your formula =(If(=) Then Else (0)) is a varaible that derives its value from . That is why the needs to appear as well.

Thanks!
-rm

Listserv Archives (BOB member since 2002-06-25)

system · October 27, 1998, 2:42pm

formulas

I haven’t been watching this thread too carefully so please excuse me if I cover territory that I haven’t seen.

I used this method to YTD and MTD type issues. I aliased the main fact table and linked it to itself in the Universe by everything BUT the date and fact fields.

For example, the fact table (ALPHA) has columns A, B, C, Date, Number. The alias (BETA) would have the same columns. The link would be joined on columns A, B, C.

Then I create an object in universe called YTD Number. The select statement is a SUM on BETA.Number. The Where Clause includes a BETA.Date <= ALPHA.Date.

This way, when the query picks a date through a pick list or Sysdate, that date is locked in as the ALPHA.Date. Then when the query brings back the YTD Number it will automatically roll up to the picked date.

The added bonus of this is that I can pull back Current Month Numbers AND YTD Numbers in the same query and automatically relate them.

I’ve lost track of the original intent of your needs but maybe this would help. Otherwise, I hope I didn’t tick people off too much if I’m off topic.

Hope this helps!
David Jelinek

Listserv Archives (BOB member since 2002-06-25)

system · October 27, 1998, 3:28pm

formulas

Thanks for the reply. This is a different direction then what I was trying.

On Tue, 27 Oct 1998 09:42:04 -0500, David Jelinek 7283 DAVID.JELINEK@OPC.COM wrote:

For example, the fact table (ALPHA) has columns A, B, C, Date, Number. The alias (BETA) would have the same columns. The link would be joined on columns A, B, C.

Then I create an object in universe called YTD Number. The select statement is a SUM on BETA.Number. The Where Clause includes a BETA.Date <= ALPHA.Date.

This way, when the query picks a date through a pick list or Sysdate, that date is locked in as the ALPHA.Date. Then when the query brings back the YTD Number it will automatically roll up to the picked date.

The added bonus of this is that I can pull back Current Month Numbers AND YTD Numbers in the same query and automatically relate them.

I 'm a bit unclear as how this would work. Do you have a sample of the generated SQL that would get ytd and mtd? Like this: select y.POL_KEY,y.COMM_PRCS_DT,sum(y.WTG_CRDT_COMM_AMT),sum(m.WTG_CRDT_COMM_AMT)
FROM COMMISSIONS y,COMMISSIONS m
where
y.COMM_KEY = m.COMM_KEY and
y.COMM_PRCS_DT <= m.COMM_PRCS_DT and
m.COMM_PRCS_DT >= “10/1/98”
group by y.POL_KEY,y.COMM_PRCS_DT

Thanks,
Giorgio

Listserv Archives (BOB member since 2002-06-25)

system · October 27, 1998, 8:00pm

formulas

I 'm a bit unclear as how this would work. Do you have a sample of the generated SQL that would get ytd and mtd? Like this:

select
y.POL_KEY,y.COMM_PRCS_DT,sum(y.WTG_CRDT_COMM_AMT),sum(m.WTG_CRDT_COM
M_AMT)
FROM COMMISSIONS y,COMMISSIONS m
where
y.COMM_KEY = m.COMM_KEY and
y.COMM_PRCS_DT <= m.COMM_PRCS_DT and
m.COMM_PRCS_DT >= “10/1/98”
group by y.POL_KEY,y.COMM_PRCS_DT

Ok, let me try ti but this is off the top of my head. I did it a year ago…

Comments follow each where clause with ←

Something like this…

Assume Date_Table is simply one field (DATE). Acts as a look-up Fact_Table is Five fields (A, B, C, Date, Number)

SELECT
a.DATE, sum(ALPHA.Number), sum(BETA.Number) FROM
Date_table a, Fact_table ALPHA, Fact_Table BETA WHERE
a.date = Sysdate (<=whatever function you need for today’s date) AND (ALPHA.A = BETA.A AND ALPHA.B = BETA.B AND ALPHA.C = BETA.C) (<- That’s the main join)
AND A.DATE = ALPHA.Date (<- connects the look-up) AND BETA.Date <= ALPHA.Date (<- The YTD Join) GROUP BY
A.DATE

The result set should give you today’s date, the total for that date and the year to date total for that date.

For month to date, you’d need to join based on a TO_CHAR function (in Oracle) to link the months. A little more challenging but doable.

Let’s take this offline if we need to go into more detail. I can take you through it live on Designer if need be.

Drop me a line at “david.jelinek@intsource.com” if I’m still not clear. I can see it in my head, just can’t get the telepathy thing going today! <grin!>

Hope that helps!
David Jelinek

Listserv Archives (BOB member since 2002-06-25)