Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am looking for a way to 'mark' all identical dimension values if a certain expression has a negative outcome for at least one of them.
Please take a look at the excel file -> I am looking for a way to mark all dimension values 'A' because for one of them there is a negative value.
Maybe important to know is that the expression (for what we are looking for negative values) is an if-expression combined with an above() function
thanks
Davy
You can use aggr for that - something like
Min(TOTAL<ARTICLE> Aggr(Sum(STOCK) < 0, ARTICLE, DATE))
It will return -1 for every ARTICLE for which stock was <0 no matter for which month, you can put it in color expression for dimension as a condition to turn on some specific color.
I'm not sure what would be the interplay with Above() formula (which would go where I put "Sum(STOCK)"), but it's worth trying out.
Hi Jakub
I’ve tried your solution but it just gives me nothing (empty values), not even a calculation error.
Even a simple min(aggr()) function to determine the lowest value for an article gives an ‘empty’…
Must have something to do with the above() function and/or with sorting of my table. The sorting in a specific way is what makes my table useful because only then the above() function gives me the wanted results. And apparently I can not detect the lowest value for each article…
Any other ideas?
Davy
Van: Jakub Michalik
Verzonden: vrijdag 2 december 2016 16:50
Aan: Davy Michiels
Onderwerp: Re: - How to mark all dimensions with same value if one of them has certain value in expression
I created a simple example based on the Excel data you have attached in first post - I have achieved what you want in principle, but probably your real use case is more complicated, so I don't know if you will be able to make use of it. But feel free to check the attached qvw file, and maybe it will point you in the right direction.
I had to keep in mind two things - first, aggr() produces "virtual" table that has dimensions always sorted in their load order, and second, above() operates within a column segment (here, column segment is defined by Article dimension). And it assumes there will be no more than 1000 rows for a single Article (because I use three argument version of Above, and you can't specify "whole column segment" with it, it has to be a number)