Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Sum(Descendants) in MDX

Dear,

I am a newby on QlikView. Please have a look at my application and guild me how to get the result. On SQL Analysis Services, I can get the result by using Sum(descendants,... but on QlikView, I have no idea.

Thank you in advance.

Anote

1 Solution

Accepted Solutions

Sum(Descendants) in MDX

Hi,

    Replace your second expression i.e. SalesAmount with following expression.

    sum({<#Equipment = {">0"}>}SalesAmount)

Regards,

Kaushik Solanki

16 Replies

Sum(Descendants) in MDX

Hi,

    Replace your second expression i.e. SalesAmount with following expression.

    sum({<#Equipment = {">0"}>}SalesAmount)

Regards,

Kaushik Solanki

Not applicable

Sum(Descendants) in MDX

Hi Kaushik.solanki,

Thank you for your respond. I'm sorry that I gave a bad example. I don't know how to upload the .QVW again so I attached images. In my case is there are two records of customer 1004. How can I get the expected result.

Best Regards,

Anote

Source.pngFormula.pngQlikView.png

nagaiank
Valued Contributor III

Re: Sum(Descendants) in MDX

In order to get the last column also per your requirement, use the following expression for the last column

Sum(if(#Equipment >0, SalesAmount,0)) / Sum(#Equipment)

Not applicable

Sum(Descendants) in MDX

Hi Kaushik.solanki,,

sum({<#Equipment = {">0"}>}SalesAmount) / sum(#Equipement)

Sum(if(#Equipment >0, SalesAmount,0)) / Sum(#Equipment)

Both of them, give me the same result but it is not what I want. This is becasue there is two records of customer 1004.

In SQL Analysis Services, I can get the answer by using this MDX.

sum(Descendants([Customer].CurrentMember,[Customer].Customer),

(iif ([Measures].[#Equipment)] > 0, [Measures].[SalesAmount] / , 0))) / [Measures].[#Equipment]

Please advise. I will re-post again in order to attach the .QVW.

Best Regards,

Anote

Not applicable

Re: Sum(Descendants) in MDX

Hi,

Please refer to the attachment.

I have created one table - "Ravi"

You cannot get answer as 400 for Location "1" as Customer 1004 has one of the equipment as 0.

As you said, we want to neglect zeroes.

Also, you can refer to expressions I have used.

I hope this explains solution for you.

Let me know further queries if any.

Not applicable

Sum(Descendants) in MDX

HI,

in January Customer = 1001 and #Equipment = 1 and SalesAmount = 100

in January Customer = 1002 and #Equipment = 2 and SalesAmount = 300

in January Customer = 1003 and #Equipment = 0 and SalesAmount = 50

In January Customer = 1004 and #Equipment = 0 and SalesAmount = 100

In Febuary Customer = 1004 and #Equipment = 1 and Sales Amount = 300

Is there any possible way to get 400 (not 300) for Customer 1004?

I want to group by Customer before checking #Equipemnt.

And the expected total = 800 (100+300+400)

Re: Sum(Descendants) in MDX

Hi,

   Can you please upload the file.

   How to upload the qvw file. ->  When you reply to any post, you will see "Use Advanced Editor"

   Click on it. You will see an option named Attach Files.

   Browse the file from there  and it will be attached to your post.

Regards,

Kaushik Solanki

Not applicable

Re: Sum(Descendants) in MDX

Hi,

I have uploaded the .QVW.

Best Regards

Anote

Re: Sum(Descendants) in MDX

Hi,

   Try this in your Sales with Equipment Expression.

   sum( {$<Customer = P({1<#Equipment={">0"}>} Customer)>} SalesAmount ) / sum(#Equipment)

Regards,

Kaushik Solanki

Community Browser