Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Replace your second expression i.e. SalesAmount with following expression.
sum({<#Equipment = {">0"}>}SalesAmount)
Regards,
Kaushik Solanki
Hi,
Replace your second expression i.e. SalesAmount with following expression.
sum({<#Equipment = {">0"}>}SalesAmount)
Regards,
Kaushik Solanki
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
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)
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
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.
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)
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
Hi,
I have uploaded the .QVW.
Best Regards
Anote
Hi,
Try this in your Sales with Equipment Expression.
sum( {$<Customer = P({1<#Equipment={">0"}>} Customer)>} SalesAmount ) / sum(#Equipment)
Regards,
Kaushik Solanki