BottomSum Function in MDX

22
5724

Friends,

This funcmtion is very much similary to the functions BottomCount and BottomPercent. Please find the Major Difference between the all three functions –

1) BottomCount – Orders the set and then returns specified number of tuples in the specified set with the lowest values will be pulled.

2) BottomPercent – Orders the set and then returns tuples from the bottom of the set with the lowest values whose cumulative total is less than or equal to the supplied percentage.

3) BottomSum – Orders the set and then returns the summed value of a numeric expression across a supplied set.

In this post we are gonna discuss about BottomSum Function. This function will do TWO jobs –

1) Sorts the set based on numeric parameter in Ascending order.

2)  Returns tuples from the bottom of the set with the lowest values whose cumulative total is less than or equal to the supplied Number.

Syntax –

BottomSum(Set_Exp,Value,Numeric_Exp)

Set_Exp – Valid MDX Set Expression

Value – It specifies the Total Value of Cumulative sum of records to be returned.

Numeric_Exp – It specifies on what column basis the set needs to be ordered.

The BottomSum function sorts a set in ascending order, then returns tuples from the bottom of the set with the lowest values whose cumulative total is less than or equal to the supplied value. This function returns the smallest subset of a set whose cumulative total is at least equal or near to the specified value. The returned elements are ordered largest to smallest.

PFB sample query Which pulls 10 Percent of Products based on Sales Amount.

select [Measures].[Sales Amount] on 0,
BottomSum([Dim Product].[Products].[Products],10000,[Measures].[Sales Amount]) on 1
from [Adventure Works]

If you see the above screenshot, the sum of returned values looks like more than the value specified and it is becausxe that the BOTTOMSUM function returns NEAREST value to the specified value in parameters. It is returning soo many records among which most are having NULL as Sales Amount. As we are asking for Bottom Cumulative sum of records based on Sales Amount, it is pulling all the  NULL records and PFB the MDX query to eliminate NULLs from output –

select [Measures].[Sales Amount] on 0,
BottomSum(Filter([Dim Product].[Products].[Products],
NOT ISEMPTY([Measures].[Sales Amount])),10000,[Measures].[Sales Amount]) on 1
from [Adventure Works]

This is a very useful function in cases where the user wish to see the Bottom Products (or simply least sales products) which is contributing some %ge of sales. So, Please don’t ignore it .. :)

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V

 

22 COMMENTS

  1. In cases of overdose, standard supportive measures should be adopted as needed soft tab cialis This is a common pattern of usage in institutions that do not subscribe to large numbers of journals, but rely on package subscriptions with a few big publishing firms

  2. Its natural healing properties and anti cancer benefits will no doubt continue to be studied extensively in years to come lasix for edema The most common AEs were arthralgia, pain in extremity, back pain, and fatigue

  3. Complications of axillary lymph node dissection for carcinoma of the breast a report based on a patient survey buy priligy tablets At present, it is also recommended that starting at age 30 they undergo annual transvaginal sonographic examination of the ovaries and serum CA 125 determination

  4. 5 So are his parents and his attorneys, all of whom have received a deluge of death threats after Zimmerman was acquitted of second degree murder charges in the killing of Trayvon Martin, his parents told Barbara Walters on Monday in their first interview stromectol tablets india Perhaps because the military often practiced here, they built several small houses with natural gas stoves for cooking, which could accommodate more than 100 people the ultimate penis enlargement guide temporarily

  5. The dosage may vary based on the age, condition and medical history of the patient insurance cover viagra 00 00 Disclaimer 00 35 Intro 02 55 Guest bio 04 15 Getting to know our guest 12 18 Picks of the week 17 07 Clinical case of aortic stenosis 18 00 Types of valve procedures available and initial workup for valvular disease 26 11 Counseling patients about a heart murmur 29 33 Symptoms in valvular heart disease 31 07 Who needs an echo

  6. Moreover, cancer cells treated with inhibitors of growth factor or estrogen receptors often become resistant to therapy which is caused by changes in downstream signaling components, such as activating mutations of PI3K or Akt genes and loss of suppressors, such as PTEN buy zithromax without a prescription If you would rather go directly to IVF which bypasses almost all the steps, then the laparoscopy and endometrial biopsy are not necessary

  7. zeagra amoxicillin and tylenol cold and flu While the project may still face obstacles from protesters and over issues such as land ownership, a supportive federal government is expected to clear the path for POSCO s top concern a captive mine that will give it steelmaking raw material iron ore buy cialis professional vytorin duphaston opinie o leku Black and Hispanic Americans are more positive than whites about extending life, although the survey could not explain why

LEAVE A REPLY

Please enter your comment!
Please enter your name here

− 3 = 7