Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV Community,
I have a problem looking up prices against products. Maybe you can help me out? I have made a small reduction of the problem into a qvw.
I have two tables: Products and Netprices. For now I have concatenated both tables. A solution with qualified unconnected tables is also allowed.
For FACTTYPE = 'NetPrices' I have a CUST_NUM and ITEMID.
For FACTTYPE= 'Products' I only have ITEMID. The CUST_NUM for those rows should be retrieved from a variable vCustomer. This variable gets updated each time the user selects another customer in ListBox CUST_NUM.
The reason why I do it like this is that I try to avoid a composite key. My data is about 120000 lines of products and 12000 customers. I avoid creating CUST_NUM&'_'&ITEMID with a left join on the Products table. This would blow up hugely my data. Therefore I'm looking for a solution where the user selects a Customer. This selection is then stored into vCustomer and on that basis I would like to search for combination $(vCustomer)&'_'&ITEMID.
I thought about storing this combination into another variable, namely exp_CUST_ITEM, in order to use that variable in Set Analysis. I already succeeded retrieving that variable in the straight table through column exp_CUST_ITEM.
My problem occurs when I try to put the Netprice next to the GrossPrice (column NetPrice_NextTo) for the rows where FACTTYPE = 'Products'. At the end of the day in my App only rows for which FACTTYPE = 'Products' will be shown.
I have tried with the following Set Analysis:
max({<%KEY_CUST_ITEMID={'1108_6672014'},FACTTYPE={'NetPrice'}>} NETPRICE)
It doesn't work. I have used literals in order to be sure there's nothing wrong with the punctuation of Set Analysis. I think this should be possible because we also do it for Budget against Actuals. There we also have Budget data concatenated to Actuals data and we show it side by side. The difference is that for Budget / Actuals calculations we have the dimension in the script. We now do not have CUST_NUM on the rows of FACTTYPE = 'Products', but we have the variable vCustomer.
The Set Analysis formula above shows the NetPrice besides the rows of FACTTYPE NetPrice, but not on the rows of FACTTYPE = 'Products'. Maybe you know how to do this, if it can be achieved? Attached qvw.
Thank you for your cooperation.
David
Try this may be
Max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'1108_6672014'},FACTTYPE={'NetPrice'}>} NETPRICE)
Hi Sunny,
Incredible, this works!
Can you explain what it means to put TOTAL <ITEMID> in front of the set modifier?
Max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'1108_6672014'},FACTTYPE={'NetPrice'}>} NETPRICE)
Thanks.
David
Hi Sunny,
Thanks for your quick reply, I checked it out and understand now that there's a difference between changing (1) the aggregation scope for selections and (2) the aggregation scope for dimensions.
(1) is applicable through set analysis -> {}
(2) is applicable through TOTAL
You exclude all dimensions from the aggregation except <ITEMID>. Thank you so much. Have a nice Christmas.
David
Hi Sunny,
I cannot get it working with the variable exp_KEY_CUST_ITEM. My Set Analysis looks ok to me. Updated qvw.
max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'=$(exp_KEY_CUST_ITEM)'},FACTTYPE={'NetPrice'}>} NETPRICE)
Thank you for your help.
Rgds,
David
There are multiple ITEM_IDs which are possible using your condition... which ITEM_ID are you looking to display?
If it is all, then why not just this
Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE)
Hi Sunny,
Yes thank you. The user already selects the customer (Aggregation scope by selection) and you excluded ItemId from TOTAL (Aggregation scope by dimension). So you're right you can be sufficient by
{<FACTTYPE={'NetPrice'}>}
Sorry to bother you with one last question. I inserted a calculated dimension in order to show only FACTTYPE = 'Products'.
=if(FACTTYPE='Products',ITEMID,null())
and I selected Suppress when value is null.
Now my formula in column NETPRICE_NextTo doesn't work anymore. Should I leave FACTTYPE = 'NVP' in the straight table, but make these rows somehow invisible?
The purpose is that the user sees a price list with gross and net prices in two columns one besides the other, but only for the rows with 'Product' in FACTTYPE.
Rgds,
David
Instead of using a calculated dimension... try this expression with ITEM_ID as dimension
If(Only({1} FACTTYPE) = 'Products', Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE))
1) if(FACTTYPE='Products',expression) doesn't work?
2) if(Only(FACTTYPE)='Products',expression) doesn't work?
These two are the same expressions.... they don't work because when you select a CUST_NUM, FACTTYPE = 'Product' is out of selection
So, in order to make sure we see Products, we need to ignore selection in either CUST_NUM or everything.
3) if(Only({1}FACTTYPE)='Products',expression) does work? Why do you use {1}?
{1} just means that ignore selection in all fields. This might work most of the times since it is an if condition and your actual value is driven of of the expression. But there might be specific cases where you might need to create a more sophisticated set analysis.
4) Is there still need to put CUST_NUM into a variable vCustomer? I don't think so. We don't use %KEY_CUST_ITEM at all.
I think you are right
When I then apply also your if(only(FACTTYPE)='Products' on the above line of code (Straight Table C.) everything seems ok. Should I be worried or is it ok?
As long as you get your desired output based on multiple tests where you select and un-select different selections that your users will make, I won't be to worried. The solution to a problem can have multiple solution, but they all might give different results on your interaction with the selections you make. Once you see an issue, you can fine tune your expressions. There is no easy way to tell that your current expression will work 100% of the times because we might not have covered all different scenarios here.
Try this may be
Max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'1108_6672014'},FACTTYPE={'NetPrice'}>} NETPRICE)
Hi Sunny,
Incredible, this works!
Can you explain what it means to put TOTAL <ITEMID> in front of the set modifier?
Max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'1108_6672014'},FACTTYPE={'NetPrice'}>} NETPRICE)
Thanks.
David
Check this out
Hi Sunny,
Thanks for your quick reply, I checked it out and understand now that there's a difference between changing (1) the aggregation scope for selections and (2) the aggregation scope for dimensions.
(1) is applicable through set analysis -> {}
(2) is applicable through TOTAL
You exclude all dimensions from the aggregation except <ITEMID>. Thank you so much. Have a nice Christmas.
David
Hi Sunny,
I cannot get it working with the variable exp_KEY_CUST_ITEM. My Set Analysis looks ok to me. Updated qvw.
max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'=$(exp_KEY_CUST_ITEM)'},FACTTYPE={'NetPrice'}>} NETPRICE)
Thank you for your help.
Rgds,
David
There are multiple ITEM_IDs which are possible using your condition... which ITEM_ID are you looking to display?
If it is all, then why not just this
Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE)
Hi Sunny,
Yes thank you. The user already selects the customer (Aggregation scope by selection) and you excluded ItemId from TOTAL (Aggregation scope by dimension). So you're right you can be sufficient by
{<FACTTYPE={'NetPrice'}>}
Sorry to bother you with one last question. I inserted a calculated dimension in order to show only FACTTYPE = 'Products'.
=if(FACTTYPE='Products',ITEMID,null())
and I selected Suppress when value is null.
Now my formula in column NETPRICE_NextTo doesn't work anymore. Should I leave FACTTYPE = 'NVP' in the straight table, but make these rows somehow invisible?
The purpose is that the user sees a price list with gross and net prices in two columns one besides the other, but only for the rows with 'Product' in FACTTYPE.
Rgds,
David
Instead of using a calculated dimension... try this expression with ITEM_ID as dimension
If(Only({1} FACTTYPE) = 'Products', Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE))