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

[#2]

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

[#3]

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

[kaushiknsolanki]

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

[#5]

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

[kaushiknsolanki]

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

[#7]

Hi,

Looking forward to see your suggestion.

Best Regards,

Anote

[#8]

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