Showing posts with label sales. Show all posts
Showing posts with label sales. Show all posts

Wednesday, March 7, 2012

how to calculate prior period with 52 weeks ?

I defined the calc for prior period for sales amount. But is it possible to get last 52 weeks from the current period?

how to define in this calc?

-- PRIOR PERIOD CALCULATIONS

([Time Calculations].[Prior Period]=

IIF(([Date].[Fiscal Hierarchy].currentmember.lag(1),

[Time Calculations].&[ Current Period])=0,null,

([Date].[Fiscal Hierarchy].currentmember.lag(1),

[Time Calculations].&[ Current Period]))

);

If your hierarchy has a week-level, then I believe what you are looking for is this:

Code Snippet

SET [Time Calculations].[Prior Period] AS 'LastPeriods( 52, [Date].[Fiscal Hierarchy].currentmember )

This gives you a set of the current week and the previous 52 weeks. Assuming [Date].[Fiscal Hierarchy].currentmember is at week-level.

Best regards

- Jens

how to calculate prior period with 52 weeks ?

I defined the calc for prior period for sales amount. But is it possible to get last 52 weeks from the current period?

how to define in this calc?

-- PRIOR PERIOD CALCULATIONS

([Time Calculations].[Prior Period]=

IIF(([Date].[Fiscal Hierarchy].currentmember.lag(1),

[Time Calculations].&[ Current Period])=0,null,

([Date].[Fiscal Hierarchy].currentmember.lag(1),

[Time Calculations].&[ Current Period]))

);

If your hierarchy has a week-level, then I believe what you are looking for is this:

Code Snippet

SET [Time Calculations].[Prior Period] AS 'LastPeriods( 52, [Date].[Fiscal Hierarchy].currentmember )

This gives you a set of the current week and the previous 52 weeks. Assuming [Date].[Fiscal Hierarchy].currentmember is at week-level.

Best regards

- Jens

Friday, February 24, 2012

How To Calculate Deviation in Matrix?

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product X1 100 0 110 10 130 20 100 -30 90 -10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me?

Thank You.

Balwant Patel.

I do not think there is a striaght-forward formula for this since use of the Previous function is not supported in the Matrix data cell. You can either try calculating the deviation from the query itself, or implement it in the following manner using Custom Code:

Place the following VB.Net code in the code tab under report properties:

Shared Public PrevSales as Integer

Public Function CalDeviation(ColCount as Integer,CurSales as Integer) as Integer
Dim deviation as Integer

If ColCount=1 Then
PrevSales=CurSales
deviation=0

Else
deviation=CurSales-PrevSales
PrevSales=CurSales

End If

Return deviation

End Function

Then use the following call to the function in the textbox where you want to show the deviation:

=Code.CalDeviation(RunningValue(Fields!Month.Value,Count,"matrix1_Product"),Sum(Fields!Sales.Value))

Here, I have assumed the names Product, Month & Sales for the respective fields. Also, the Matrix Row Grouping is on Product & Column Grouping is on Month.

Hope this suits your purpose

-Aayush


|||

Hi Aayush,

Thank you very much for your response...

I tried the code given by you...But when try to write the code in code window as below:

=Code.CalDeviation

the function .CalDeviation doesn't come up....

Thank you,

Regards,

Balwant Patel.

|||

Hi Aayush,

The idea that you give me works now but it will not work when i view Report for Year Or Quarter .....

Please help me on this issue...

Thank you,

Regards,

Balwant Patel.

|||I guess it depends on your requirement, and how your Matrix is designed. What kind of functionality do you desire? Suppose you replace your Column Grouping from Month to either Year or Quarter, then it will work. If your column grouping changes on some condition etc, then it might be a bit more complicated.|||

Hi Aayush,

Thank you very much for your response...

I did solve the problem....I applied following logic to solve the problem...In my Data Table I add Six Columns Like CurrMonthSales , PrevMonthSales , CurreQuarterSales , PrevQuarterSales , CurrYearSales , PrevYearSales...And then Updates these columns through Stored proc...And Then Based in Inscope Function...I Apply The Formula.

Thank you very much again..

Regards,

Balwant Patel.

How To Calculate Deviation in Matrix?

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product X1 100 0 110 10 130 20 100 -30 90 -10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me?

Thank You.

Balwant Patel.

I do not think there is a striaght-forward formula for this since use of the Previous function is not supported in the Matrix data cell. You can either try calculating the deviation from the query itself, or implement it in the following manner using Custom Code:

Place the following VB.Net code in the code tab under report properties:

Shared Public PrevSales as Integer

Public Function CalDeviation(ColCount as Integer,CurSales as Integer) as Integer
Dim deviation as Integer

If ColCount=1 Then
PrevSales=CurSales
deviation=0

Else
deviation=CurSales-PrevSales
PrevSales=CurSales

End If

Return deviation

End Function

Then use the following call to the function in the textbox where you want to show the deviation:

=Code.CalDeviation(RunningValue(Fields!Month.Value,Count,"matrix1_Product"),Sum(Fields!Sales.Value))

Here, I have assumed the names Product, Month & Sales for the respective fields. Also, the Matrix Row Grouping is on Product & Column Grouping is on Month.

Hope this suits your purpose

-Aayush


|||

Hi Aayush,

Thank you very much for your response...

I tried the code given by you...But when try to write the code in code window as below:

=Code.CalDeviation

the function .CalDeviation doesn't come up....

Thank you,

Regards,

Balwant Patel.

|||

Hi Aayush,

The idea that you give me works now but it will not work when i view Report for Year Or Quarter .....

Please help me on this issue...

Thank you,

Regards,

Balwant Patel.

|||I guess it depends on your requirement, and how your Matrix is designed. What kind of functionality do you desire? Suppose you replace your Column Grouping from Month to either Year or Quarter, then it will work. If your column grouping changes on some condition etc, then it might be a bit more complicated.|||

Hi Aayush,

Thank you very much for your response...

I did solve the problem....I applied following logic to solve the problem...In my Data Table I add Six Columns Like CurrMonthSales , PrevMonthSales , CurreQuarterSales , PrevQuarterSales , CurrYearSales , PrevYearSales...And then Updates these columns through Stored proc...And Then Based in Inscope Function...I Apply The Formula.

Thank you very much again..

Regards,

Balwant Patel.

How to caculate result throw time range using the cubes?

How can I calcualte the sales value from 1/15/2005 to 4/23/2005 Using Sales Cube?

Use calculated member with a sum function. The set in the sum function should be specified as time range between members of Time (or Date). You can easily do the range as "member1 : member2", like in this example:

select [Date].[Date].&[1] : [Date].[Date].&[271] on 0 from [Adventure Works]

Sunday, February 19, 2012

How to build a 10 best topcount...?

I'm beginer. I want to build a 10 best brands unit sales in (Foodmart database). where do I start?(From sales cube or shared dimension), pls experts told how to do it?

Use Topcount MDX function.

For example

SELECT TopCount
({[Geography].[Geography].[City].Members
*[Date].[Fiscal].[Fiscal Year].[FY 2003]}
, 5
, [Measures].[Reseller Sales Amount]
) ON 0,
[Product].[Product Categories].Bikes ON 1
FROM [Adventure Works]

See some more

http://msdn2.microsoft.com/en-us/library/ms144792.aspx

http://cwebbbi.spaces.live.com/PersonalSpace.aspx?_c11_blogpart_blogpart=blogview&_c=blogpart&partqs=amonth%3D5%26ayear%3D2005

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to build a 10 best topcount...?

I'm beginer. I want to build a 10 best brands unit sales in (Foodmart database). where do I start?(From sales cube or shared dimension), pls experts told how to do it?

Use Topcount MDX function.

For example

SELECT TopCount
({[Geography].[Geography].[City].Members
*[Date].[Fiscal].[Fiscal Year].[FY 2003]}
, 5
, [Measures].[Reseller Sales Amount]
) ON 0,
[Product].[Product Categories].Bikes ON 1
FROM [Adventure Works]

See some more

http://msdn2.microsoft.com/en-us/library/ms144792.aspx

http://cwebbbi.spaces.live.com/PersonalSpace.aspx?_c11_blogpart_blogpart=blogview&_c=blogpart&partqs=amonth%3D5%26ayear%3D2005

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.