Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have this straight table - can't use a pivot instead - that I manually set different store IDs per column as a dimension. I have a product dimension in the table and 13+ columns that are calculated over the dimension by hardcoding the store ID in the set analysis.
Everything work as expected, but I need to get the store id count for each row where the store has sold at least 0.01 (1%) of the total for that product (within all stores). I'll use this to color format the cells.
The following expression almost does the job:
Count(Distinct
{<store_id= {"=((Sum({<sale_type= {'V'}>} erp_total_value) - Sum({<sale_type= {'D'}>} erp_total_value)) / (Sum(total <store_id> {<sale_type= {'V'}>} erp_total_value) - Sum(total <store_id> {<sale_type= {'D'}>} erp_total_value))) >= 0.01"}>} store_id)
It does count correctly some rows, but not always. It seems that it's not respecting the month and year filter and it sums everything for each store, regardless of the period. So if a store had sold this product in the past and even if it didn't sell anything in the month displayed, the store is still being counted.
It's kinda tricky for me to visualize what's happening because there is no store dimension within the table, only the hardcoded columns that hold the % value. I need to get the count of stores so I can calculate the "perfect" distribution (100% / x) and create a color format rule based on this value.
In the print above (ignore column 'aa'), you can see the last 2 lines are not being counted properly, while the line with the count of 3 is correct.
I'm pretty sure this must be simple and something within the set analysis. Any help is appreciated.
PS:
Note that no product dimension is being used in the expression, because I assumed it'd calculate over the product dimension that is populating the lines. And it does seems to respond to the product dimension;
Accepted Solutions
