Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I have a question regarding a set-analysis: If I calculate a formula using set-analysis with a certain selection, is it possible to additionally select the dimension values (dynamically)?
It that's possible within the set-expression, how would the syntax be?
As an example I got the following table:
ProductType | Customer (cyclic group) | year | sold per year |
shoes | test1 | 2007 | 350 |
2008 | 350 | ||
2009 | 350 | ||
test3 | 2008 | 350 | |
test8 | 2007 | 350 | |
test2 | 2008 | 350 | |
total | 0 | ||
Sold per year contains the following formula:
if(_Category='footwear' and Index('31,32,33,34',Right(CatID,2))>0,Count( distinct total {_Category = {"shoes","shoeslaces"},ProductType = {*}) sold_ID),sum(sold)))
What I need to achieve is that if certain IDs of our "footwear"-category apply, it should count the distinct IDs of another category, since I don't have a summable amount of sold units for these. Sadly, this formula doesn't take into consideration the dimensions in my pivot-table - leading to a amount of 350 wich is the number of sold units in total for ALL customers and years.
Strangely, in the total column nothing is being calculated?
Is there a way to dynamically tell the set-analysis formula to calculate for each line of the given table and select the corresponding values?
(e.g. ProductType = "shoes",Customer = "test1", year = "2007").
It would be easy to assist if you upload a QVW with some sample data.
Okay, i've found a solution to this. Adding in the dimension/group names after the total modifier seems to do the trick.
if(_Category='footwear' and Index('31,32,33,34',Right(CatID,2))>0,Count( distinct total <CustomerGrp,Year> {_Category = {"shoes","shoeslaces"},ProductType = {*}) sold_ID),sum(sold)))