topic Re: Sum(Descendants) in MDX in QlikView

Sum(Descendants) in MDX

Mon, 27 Jun 2011 10:35:56 GMT

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

Sum(Descendants) in MDX

kaushiknsolanki — Mon, 27 Jun 2011 10:49:51 GMT

Hi,

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

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

Regards,

Kaushik Solanki

Sum(Descendants) in MDX

Mon, 27 Jun 2011 12:02:24 GMT

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

Re: Sum(Descendants) in MDX

nagaiank — Mon, 27 Jun 2011 13:51:57 GMT

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)

Sum(Descendants) in MDX

Tue, 28 Jun 2011 02:28:56 GMT

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

Re: Sum(Descendants) in MDX

Tue, 28 Jun 2011 05:02:22 GMT

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.

Sum(Descendants) in MDX

Tue, 28 Jun 2011 05:44:51 GMT

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

kaushiknsolanki — Tue, 28 Jun 2011 06:09:31 GMT

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

Re: Sum(Descendants) in MDX

Tue, 28 Jun 2011 06:17:25 GMT

Hi,

I have uploaded the .QVW.

Best Regards

Anote

Re: Sum(Descendants) in MDX

kaushiknsolanki — Tue, 28 Jun 2011 06:35:34 GMT

Hi,

Try this in your Sales with Equipment Expression.

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

Regards,

Kaushik Solanki

Sum(Descendants) in MDX

Tue, 28 Jun 2011 06:42:55 GMT

"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)"

Hi,

In that case, you will have to create one more column in Excel.

Those which you want to add can be given no. 1 and neglected can be 0.

This is to identify uniqueness of each line item of database.

e.g.

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

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

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

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

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

Now you can create formula based on this Column.

Re: Sum(Descendants) in MDX

Tue, 28 Jun 2011 06:51:55 GMT

Hi Kaushik.solanki,

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

This expression is perfect for my case. Please explain how it work especially $, P and 1, so that I can apply to other cases.

Highly appreciate your help.

Best Regards,

Anote

Re: Sum(Descendants) in MDX

kaushiknsolanki — Tue, 28 Jun 2011 06:59:55 GMT

Hi,

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

From above expression the sub expression i.e.

P({1<#Equipment={">0"}>} Customer)>}

will give you the Customers which have #Equipment > 0.

and then the main Expression says.

Give me the SalesAmount where Customers who have #Equipment >0.

Regards,

Kaushik Solanki

Re: Sum(Descendants) in MDX

Tue, 28 Jun 2011 07:15:17 GMT

Hi Kaushik.solanki,

One more question, if I filter data only Jan 1,Jan 2, Jan3 and Jan 5, I expect to get 133.33 [(100+300)/3] but I got 166.66. How can I fix this?

Best Regards,

Anote

Re: Sum(Descendants) in MDX

kaushiknsolanki — Tue, 28 Jun 2011 07:31:46 GMT

Hi,

According to Formula it is correct cause in the expression we are saying give all customers who have #Equipment >0 so you will also get 1004 and thus it is taking value i.e. 100 even if the #Equipment is 0.

Let me think on this. will get back to you.

Regards,

Kaushik Solanki

Re: Sum(Descendants) in MDX

Tue, 28 Jun 2011 09:22:21 GMT

Hi,

Looking forward to see your suggestion.

Best Regards,

Anote

Sum(Descendants) in MDX

Thu, 30 Jun 2011 08:44:32 GMT

Dear,

I have got my answer. It is not perfect because if I have more dimensions such as Equipment Type, I have to add Equipment Type into the expression. I hope to have a better way of doing. : (

sum( {$<Customer = P({1<#Equipment={">0"}, Date={"$(=concat(distinct Date,'","'))"}>}) >} SalesAmount ) / sum(#Equipment)

Best Regards,

Anote