
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Looking forward to see your suggestion.
Best Regards,
Anote

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
-
- 1
- 2
- Next Replies »