Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody!
I have a straight table where i need to mark some cells with green color according to specific condition: If average of the values of next 3 months is greater than 0.5, then mark with green color.
The format of the months field is : MMM-YY
Any ideas how an expression of such type can look like?
Thanks!
Assuming your dimension Month is ordered asc:
Create a background color attribute expression (open the attribute expressions by clicking on the small plus sign next to expression label on upper left on expression tab), with definition
=if( rangeavg( below( YourExpression , 1, 3)) > 0.5, lightgreen())
I dont have Month dimension in my table, this field is in the script but not used in that particular table
I think it can be solved with Set analysis.
Something like this i guess:Avg({<Month={">=$(=Num(Date(AddMonths(Max(Month),3)))) "}>} [Values])
But the question is why this part isn't working Date(AddMonths(Max(Month),3))? Is this because of my Month format?
A solution needs some knowledge about your setting, so it would be better to fully describe your chart dimensions / expressions, relevant data model etc.
So it's just guessing maybe something along these lines:
=if (avg( {<Month = {">=$(=monthstart(today(),1))<=$(=monthstart(today(),3))"}>} Value) > 0.5, lightgreen())
assuming your Month has a numeric representation with monthstart date (like QV will create when using its monthstart() function in script).
Guys, any ideas?? please help!