Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
maahivee
New Contributor III

Eliminating the selection of one dimension.

Hello Guys,  I have same issue with a slightly different request.

I now have 2 dims order number and item number, but the point is i want to show the Sum(sales) for each item number but regardless the order number

To elaborate- I want to see the sum(sales) whenever i select an order number all the lines for that order come up but they show only the data for that order.

But what i want to do is show the Sum(sales) for all the items in that order regardless of the order number, Whatever is the entire sales for that item not only for that particular order. 


But the users want to still filter it based on the order number and look what are the entire sales for all the items in that order. How can i achieve this.


Sum({$<[Order Number]=>}  [Sales])

Sum(Total [Sales])

Sum(All<[Item Number]>[Sales])


I have used all these expressions but nothing is working.


Thanks in advance.

Tags (2)
1 Solution

Accepted Solutions
ramoncova06
Valued Contributor III

Re: Eliminating the selection of one dimension.

this should work

Sum({$<[Order Number]=,[Item Number] =p({$})>}  [Sales])

6 Replies
ziadm
Valued Contributor

Re: Eliminating the selection of one dimension.


Your Syntax seems to be fine ..

Sum({$<[Order Number]=>}  [Sales]) 

This should work as this gives you sum of Sales of the selected Item Numbers regardless of the order number.

I think you have to revisit you data model and make sure that your Fact table and dimension table are linked correctly.

You may post a picture of you data model



maahivee
New Contributor III

Re: Eliminating the selection of one dimension.

Thank You Ziad for the reply, Yes actually that above syntax is working.

But the problem with it is that the user wants to select a particular order and if that order has 10 items it should show only those 10 rows. But since we are eliminating the selection of order Number from that expression it is showing all the lines for all the orders. Also it is not summing the sales only by item Number. But by both order and item number.

Sum({$<[Order Number]=>}  [Sales]) -- is just letting us not to select the order number but not meeting the actual requirement which is showing the sum of total sales for each item in that order.

say we have 20 orders which have the item 123, in that case all the 20 rows for that item should show 500$ if 500$ is the total sales since the beginning of the item creation.

Not applicable

Re: Eliminating the selection of one dimension.

Hi Maahivee,

Try this - sum({1<[Item Number]=P([Item Number])>}Sales)

Cheers

ramoncova06
Valued Contributor III

Re: Eliminating the selection of one dimension.

this should work

Sum({$<[Order Number]=,[Item Number] =p({$})>}  [Sales])

maahivee
New Contributor III

Re: Eliminating the selection of one dimension.

Thanks Ramon for the answer, Can you please explain the expression in a brief.

ramoncova06
Valued Contributor III

Re: Eliminating the selection of one dimension.

with [Order Number]= you are telling it to ignore the sale order selection and [Item Number] =p({$}) is bypassing the exclusion and bring in only the item numbers that belong to your current selection