Showing posts with label calculates. Show all posts
Showing posts with label calculates. Show all posts

Tuesday, March 27, 2012

calculated member

Hi,

I created the calculated member ( mkdn dollars/ sale dollars) in the cube but when I see the variance(Prior year Chg), it seems it calculates the mkdn dollars - prior year chg / sales dollars -prior year chg

Here are samples for better understanding:

==================================================================

1st week - 2008

current period prior period prior period chg

-

Mkdn % 48.3 % 46.99% 79.82 %

mkdn dollars 524,725 490, 198 34,526

sales dollars 1086,433 1043,178 43,254

-

-- Prior Year Chg

-

([Time Calculations].[Prior Year Chg]=

[Time Calculations].&[Current Period] - [Time Calculations].[Prior Year]);

==================================================================

If I want to get prior priod chg ( 48.3 -46.99) based on mkdn % then I should I change the prior year chg calc ?

Also, I tried to create named calcuation in DSV for mkdn % but it returns a wrong value. (why does not return a right

value? something missing?) This is the expression I created as under:

==================================================================

CASE
WHEN [Net_Sale_Dollars] = 0 THEN NULL
ELSE [POS_Mrkdn_Dollars]/[Net_Sale_Dollars]
END

==================================================================

Please give me any comments

Thank you in advance.

You already tried to use the function ParalellPeriod and PeriodsToDate?

regards!

|||

Check the order of calculations in the cube MDX script, and make sure that [Mkdn %] precedes [Prior Year Chg], so that [Mkdn %] gets computed prior to [Prior Year Chg].

"Also, I tried to create named calcuation in DSV for mkdn %" - but what measure aggregation function are you applying to this, since it is a ratio?

|||

If I put mkdn % in the measure scope then it gives an error as follow:

--

Error 1 A set has been encountered that cannot contain calculated members. 0 0

--

[mkdn %-] calculation is as follows:

CREATE MEMBER CURRENTCUBE.[MEASURES].[ Mkdn %]

AS IIF ([Measures].[Net Sale Dollars] = 0, NUll,

[Measures].[POS Mrkdn Dollars]/[Measures].[Net Sale Dollars]),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

calculated member can't be contained in time calculation?

And you are right, since this measure is ratio I can't you any aggregate function. ( I'm so stupid Smile )

Please give me any comments.

Thanks.

|||"Error 1 A set has been encountered that cannot contain calculated members" - if you received this error when dropping the time calculation in the top (subcube) filter in the BIDS cube browser, try placing it in the bottom (where) filter section instead, and see if you still get the error.|||

This error pops up when I put ' mkdn % ' calc measure in the mdx scripts (scope statement which shows all measures )

I also tried to drop the time calc in bottom filter section but it didn't work.

my question is calculated member can't be contained in measure scope in mdx script?

(it doesn't make sense to me)

What else do I need to try to correct this? Please give me a direction.

Thanks always Deepak Big Smile

|||That's a different issue - calculated member definitions have global scope, so they shouldn't be placed inside scoped calculations; but subsequent assignments to such members can be scoped.|||

Okay. you mean calculated member definitions have global scope, these measrues can't be contained in scoped calculation.

If so, to calculate the varience for calculated members, do i have to write mdx scripts instead of using Time calculation which calculate prior year, variance, variance % and so on?

And this is the only way to calculate this scenario? or is there any way to use time calculation(shell dimension) for the calculated measures?

|||"If so, to calculate the varience for calculated members, do i have to write mdx scripts instead of using Time calculation which calculate prior year, variance, variance % and so on?" - could you explain what you mean in more detail? For example, the calculations generated by the Time Intelligence Wizard are added to the cube MDX script, so I'm not sure what distinction you're drawing between time calculations and MDX scripts. Typically a calculated member will be created (Create Member ..), and then scoped assignment(s) will be used to apply calculations to the appropriate subcube(s) containing the member.|||

my mdx script is exactly same as you described.

And I manually created time calc as under:

/* calculated member */

create member prior year

create member prior year chg

create member mdkn %

AS IIF ([Measures].[Net Sale Dollars] = 0, NUll,

[Measures].[POS Mrkdn Dollars]/[Measures].[Net Sale Dollars]),

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

.....

/* scope for specific measures */

>> when I added calculated member ( mkdn %) then it gave an error and as you said since calc members have global scope, I can't add calc member in scoped calc.

scope {

[measures].[sale dollars]

....

}

scope( [time].[calendar year].[Calendar Year].members,[Time].[TimeKey].members);

-- prior year calc
([Time Calculations].[Prior Year]=
(ParallelPeriod([Time].[Calendar Hierarchy].[Year], 1,
[Time].[Calendar Hierarchy].currentmember)
,[Time Calculations].[Current Period])
);

end scope;

-- Prior Year Chg :This calc does not depend on specific hierarchy

([Time Calculations].[Prior Year Chg]=
[Time Calculations].[Current Period] - [Time Calculations].[Prior Year]);

end scope;

-

This time calc works fine except all calculated members (current period and prior period work properly but prior year chg doesn't ; i.e. [mkdn % - prior year chg] computes each measure in current period and prior period and then recalculate the calcuated members as I explained at first. )

Hope you can get better understanding and please give me some comments.

Thanks.

|||

I was referring to calculated member creation, not to assignment - so see if you can repeat the scoped assignment separately for [mdkn %] alone, to resolve the error, like:

Code Snippet

/* scope for [mdkn %] measure */

scope([measures].[mdkn %]);

scope( [time].[calendar year].[Calendar Year].members,[Time].[TimeKey].members);


-- prior year calc
([Time Calculations].[Prior Year]=
(ParallelPeriod([Time].[Calendar Hierarchy].[Year], 1,
[Time].[Calendar Hierarchy].currentmember)
,[Time Calculations].[Current Period])
);

end scope;

-- Prior Year Chg :This calc does not depend on specific hierarchy

([Time Calculations].[Prior Year Chg]=
[Time Calculations].[Current Period] - [Time Calculations].[Prior Year]);

end scope;

|||

I put (mkdn % + all calc members) scope seperately outside the time calc sope, and repeated time calc scripts( prior year and prior year chg) with calculated members, then it works Smile

Thanks a lot !!!!

You Save me Big Smile

Sunday, March 25, 2012

Calculated Measure (Previous Act Value) Problem

G'Day all,

I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.

The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))

This works fine until I look at the totals within EXCEL 2002/2003

1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.

2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -

3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.

WAIT - There's more

4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!

Any Ideas??

Does it make sense??

Thanks

MattWell...

ParallelPeriod

Is a udf someone wrote...

How are you connecting to sql server?

Are you connecting to sql server?

Sorry...should've looked first...

I have GOT to start playing with that stuff...never had any real need...|||No, ParallelPeriod is an Analysis Services function. I don't know anything more about it than that.

From the Holy Book:

ParallelPeriod
Returns a member from a prior period in the same relative position as a specified member.

Syntax
ParallelPeriod([Level[, Numeric Expression[, Member]]])

Remarks
This function is similar to the Cousin function, but is more closely related to time series. It takes the ancestor of Member at Level (call it ancestor); then it takes the sibling of ancestor that lags by Numeric Expression, and returns the parallel period of Member among the descendants of that sibling.|||You should it's a powerful addition to SQL Server - only sometimes you can get lost in translation (so to speak) - the MDX statements (language for writing the calcs) sometimes take a while to understand. Hopefully you take a crash course and help me with my problem.

Thursday, March 22, 2012

Calculated Field (GPA)

I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.

here is a sample table:

Term

ID

LastName

FirstName

CRN

Subject

LetterGrade

Calc GPA

20072

1

Doe

John

1234

CHEM

B

20072

1

Doe

John

3214

BIO

A

20072

1

Doe

John

4321

LAW

B

20072

2

Bauer

Jack

1234

CHEM

A

20072

2

Bauer

Jack

3214

BIO

C

20072

2

Bauer

Jack

5467

FIN

B

A = 4.0

B = 3.0

C = 2.0

D = 1.0

Here ya go:

Code Snippet

declare @.t table (Term char(5), ID int, LastName varchar(50), FirstName varchar(50),

CRN int, Subject varchar(20), LetterGrade char(1))

insert into @.t

select '20072', 1, 'Doe', 'John', 1234, 'CHEM', 'B'

union all select '20072', 1, 'Doe', 'John', 3214, 'BIO', 'A'

union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'

union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'

union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIO', 'C'

union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

declare @.GradeEquiv table (LetterGrade char(1), NumericGrade numeric(2, 1))

insert into @.GradeEquiv

select 'A', 4.0

union all select 'B', 3.0

union all select 'C', 2.0

union all select 'D', 1.0

select t.Term, t.ID, t.LastName, t.FirstName, avg(ge.NumericGrade) as GPA

from @.t t

inner join @.GradeEquiv ge

on t.LetterGrade = ge.LetterGrade

where Subject in ('CHEM', 'BIO')

group by t.Term, t.ID, t.LastName, t.FirstName

|||

Try:

;with Grade

as

(

select '20072' as Term, 1 as ID, 'Doe' as LastName, 'John' as FirstName, 1234 CRN, 'CHEM' as [Subject], 'B' as LetterGrade

union all select '20072', 1, 'Doe', 'John', 3214, 'BIO', 'A'

union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'

union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'

union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIO', 'C'

union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

)

select

Term,

ID,

LastName,

FirstName,

CRN,

[Subject],

LetterGrade,

AVG(

case LetterGrade

when 'A' then 4.0

when 'B' then 3.0

when 'C' then 2.0

when 'D' then 1.0

end

) OVER(partition by Term, ID) as GPA

from

Grade

where

[Subject] in ('CHEM', 'BIO');

AMB

|||

Thanks guys, I'll try those out now.

|||

Is there a way to still show the other classes, like LAW for instance, but to still have the GPA from the other calculated classes?

|||

Try:

;with Grade

as

(

select '20072' as Term, 1 as ID, 'Doe' as LastName, 'John' as FirstName, 1234 CRN, 'CHEM' as [Subject], 'B' as LetterGrade

union all select '20072', 1, 'Doe', 'John', 3214, 'BIO', 'A'

union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'

union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'

union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIO', 'C'

union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

)

select

Term,

ID,

LastName,

FirstName,

CRN,

[Subject],

LetterGrade,

SUM(

case

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'A' then 4.0

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'B' then 3.0

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'C' then 2.0

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'D' then 1.0

end

) OVER(partition by Term, ID) /

nullif(

SUM(

case

when [Subject] in ('CHEM', 'BIO') then 1

else 0

end

) OVER(partition by Term, ID), 0) as GPA

from

Grade;

AMB

Calculated column in a table

Hi,
I have a table with a calculated column.
There is also a footer which calculates the sum in a column.
How I can calculate the sum from a caculated column?
Ex: 2 10
5 25 <- calculated column
--
7 35 <- calculated sum of column
(SRS 2000 SP2)
Thanks
EricThe best way to do this is to created a calculated field. On the field list,
do a right mouse click, add, click on calculated field. After that it is
treated like any other field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <jug@.nospam.nospam> wrote in message
news:uHCxZsEaFHA.1448@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a calculated column.
> There is also a footer which calculates the sum in a column.
> How I can calculate the sum from a caculated column?
>
> Ex: 2 10
> 5 25 <- calculated column
> --
> 7 35 <- calculated sum of column
> (SRS 2000 SP2)
>
> Thanks
> Eric
>|||Thanks for answering.
But now there's another problem.
I can't use an aggreate function in the column. The column calculates the
percentage of the first column to the Total.
Any idea jow I can do that in the report?
Thanks
Eric
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:eyqGY%23EaFHA.3712@.TK2MSFTNGP09.phx.gbl...
> The best way to do this is to created a calculated field. On the field
> list, do a right mouse click, add, click on calculated field. After that
> it is treated like any other field.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <jug@.nospam.nospam> wrote in message
> news:uHCxZsEaFHA.1448@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a table with a calculated column.
>> There is also a footer which calculates the sum in a column.
>> How I can calculate the sum from a caculated column?
>>
>> Ex: 2 10
>> 5 25 <- calculated column
>> --
>> 7 35 <- calculated sum of column
>> (SRS 2000 SP2)
>>
>> Thanks
>> Eric
>|||Hi Eric,
What aggreate function? You may refer sample report Company Sales.rdl for
more information about how to sum the columns.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for answering.
this is an too easy sample.
I have a column with values (DataBound). A second column should show the
percentage of first column to the sum.
Example:
20 20%
80 80%
-- --
100 100%
For calcualating the Second column I need to know the sum of the first
column.
How I do that?
If the second column is a 'calculated field', I can't use aggreate function
like 'Sum(column1)'.
thanks
Eric
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> schrieb im
Newsbeitrag news:MDOIi7LbFHA.2476@.TK2MSFTNGXA01.phx.gbl...
> Hi Eric,
> What aggreate function? You may refer sample report Company Sales.rdl for
> more information about how to sum the columns.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Eric,
Thanks for your patience.
For now, aggregates of aggregates are not currently supported in Reporting
Services so you will find other way to accomplish this. For example, you
could generate this data in the DataSet.\
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 20, 2012

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance

|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance

|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

|||Hi,

I am also facing same problem...do u got any solution....
plz help mesql

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Monday, March 19, 2012

Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the age
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.
Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>
|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a PHP Script that solves this problem, it's something
like this:
"
$nextbirthday =
mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*365);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:

> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> age
> do
> a
>
>
|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Márcio Santos" wrote:
[vbcol=seagreen]
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a PHP Script that solves this problem, it's something
> like this:
> "
> $nextbirthday =
> mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*365);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a computed
> field.
>
> "Uri Dimant" wrote:
|||On Wed, 8 Jun 2005 03:46:02 -0700, "Mrcio Santos" <Mrcio
Santos@.discussions.microsoft.com> wrote:

>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the age
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Mrcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thx... it works perfectly...
"Hugo Kornelis" wrote:

> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
>
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>