Sum(Descendants) in MDX

[#1]

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

[kaushiknsolanki · Accepted solution]

Hi,

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

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

Regards,

Kaushik Solanki

[#3]

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

[nagaiank]

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)

[#5]

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

[#6]

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.

[#7]

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)

[kaushiknsolanki]

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

[#9]

Hi,

I have uploaded the .QVW.

Best Regards

Anote

[kaushiknsolanki]

Hi,

   Try this in your Sales with Equipment Expression.

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

Regards,

Kaushik Solanki