MTD & YTD aggeration

building semantic layer
1

Hi All,

I am facing a peculiar suituation in MTD & YTD values.

The MTD values and YTD values(measures) are comming from ETL where MTD values are calculated based on fact granularity and in the universe these are made as measure.

For YTD values these are cummaliative(running sum) for MTD values and also in universe YTD is made as measure.

So here my scenerio is :

Months MTD values YTD values

1 12 12
2 10 22
3 5 27
- -
61(this is happening at universe level)

in the above scenerio i just want to negelect the whole sum.
But the Sum(YTD ) needs to happen.

Please advice on the same

babu_272 :india: (BOB member since 2010-07-28)

2

Please elaborate :).
If these are the values stored in the DB, then that is what you will see.

I don’t understand what you mean when you say “neglect the whole sum”.

Mak 1 :uk: (BOB member since 2005-01-06)

3

I think he means he doesn’t want the overall sum - the aggregate projection. Summing a running sum is a bit pointless.

Try not having your objects as measures - you don’t want to add them anyway since they are pre-aggregated in the database. Or leave as a measure but take off the projection. Sometimes I find with things like this that you have to define them as measure in order to display them in a crosstab, even though you are not performing any calculations.

debbie

Debbie :uk: (BOB member since 2005-03-01)

4

Hi Mak/Debiee,

Please elaborate .
If these are the values stored in the DB, then that is what you will see.

I don’t understand what you mean when you say “neglect the whole sum”.

For the above MAK if you can see my example that has been displayed i just dont want to 16 only the upper values needs to be displayed.

Debiee,

i want to have the Sum of MTD s but not the final sum.

I mean like here there are 2 levels of summing up is being done 1…st runningsum(sum(MTD)) is being done and also SUm(runningsum(Sum(MYD))) this is being happened at universe level i just dont want to have the final aggereation.

Kindly Reply

babu_272 :india: (BOB member since 2010-07-28)

5

OK, well make the projection “none” under object properties. That is what Debbie was suggesting…

Mak 1 :uk: (BOB member since 2005-01-06)

6

Hi Guys,

I am not getting the anticipated result if the projection is made None…

Anymore suggestions please?

babu_272 :india: (BOB member since 2010-07-28)

7

What DB are you using?
What is the SQL of the actual universe object measures?

In your table example, above, is that how the data exists in the DB table?

I still maintain you could be a bit clearer about your problem :).

Mak 1 :uk: (BOB member since 2005-01-06)

8

Mak1,

My DB is SQL Server 2005 the data is an example not the exact thing the SQl is just SUM(YTDMeasure) but at ETL level its made as running sum(as discussed above) on universe side its just an measure.

In the report if i use MAx([YTDMeasure] foreach([period])) i am able to get the exact anticipated value this must be applied at universe level?

an breif explanatopn of above formula is the Report is taking MAX([measure]) values for each period which is the result thats required.

So kindly suggest me how to implement the same thing in Universe level

babu_272 :india: (BOB member since 2010-07-28)

9

Well, to be frank, no wonder I didn’t know what you were on about :)?

You could Max it in the universe object and use that in conjunction with your period object?

i.e.

Max(YTDMeasure)

Mak 1 :uk: (BOB member since 2005-01-06)