Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have sales data where is same ID at customer and store dimension (Internal/external sales) and I´m trying to achieve a object where I can see total sales, i.e first column internal sales (#Store --> #Customer) and second column sales #Store -->
In other words, warehouse is selling goods to its customer (customer ID 100) and this customer (store ID 100) is selling those goods to consumers. I´m trying to add both of those sales in one row.
Previously I have done this like
if(WildMatch(#Customer,'100*'), sum({<#Store={'100'}>}Total Sales))
but now I have about 70 rows so I don´t want to do it that way.
I tried something like that but with no succees:
sum({<#Store={"=$(=Only({<Chain={'aaa'}>}#Customer))"}>}TOTAL Sales)
Only({<Chain={'aaa'}>}#Customer) <-- only this returns right customer number for each row
There is #Customer as dimension at my object.
Is there some way I can achieve what I´m wanting?
Thanks in advance!
Not entirely sure I understood what you are trying to do. Would you be able to share a sample with your expected output?
CustomerID | Warehouse sales to its customer (=CustomerID) | Store sales to its customers (StoreID = CustomerID) |
---|---|---|
100 | 1000 | 2500 |
150 | 1200 | 2400 |
200 | 1300 | 2700 |
At data there are separate rows for warehouse sales to its customer and store sales to consumers.
I´m not sure how to put this problem into words but hopefully you get my point So what I want to achieve is to see at the same time what warehouse is selling its customers (customer = store) and at the same time what stores are selling to consumers.
Dont know, exactly understood what you are looking for, I would suggest:
Take Calculated Dimension: If(#Customer like '100*', #Customer)
Expression: sum({<StoreID={'100'}>} Sales)
Thanks but is there anyway to get this work dynamically? Because I have those stores about 70 so is it possible to get dimension value for each row and refer to that value in set analysis (storeID = customerID)?