Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All
I am stuck! I am trying to get rid of the current month from a chart. I have checked the documentation and the posted answers but I am not able to solve the problem. I need your help!
Please find below the chart I am talking about:
In October 2010 for example, the line goes down to zero, because there is little activity. How can I exclude October and have only the last full month (in this case September 2010)?
I am looking forward to hearing from you. Thanks.
I have found a solution (even if it might not be very orthodox). I have added this line to the Script:
WHERE
and it works.
Thanks guys for your help!
Try somthing like sum({$<MonthYear -= {"=MonthYear=monthname(today())"}>} Sales) or an if like this sum(if(MonthYear<>monthname(today()),Sales))
The graph by default should suppress 0 and null values.
Regards.
Dear Karl
I have tried to use your expression, but Month Name is for me a dimension and not an expression. In the chart expression I use Count(APCKey). Sorry, but it does not seem to be working (I have also tried to use it as a calculated dimension, receiving an error).
Thanks.
Try this expression please:
Count(if [Month Name] <> date(today(), 'MMM YY'), APCKey)
Let me know if this works.
If not please post a copy with that chart - so that I can take a look at it.
What I'm trying to do is compare the dimension with the value of today() to tell QlikView if it should calculate the expression or no. So it is ok to use the dimension in the expression. If it doesn't work it is problably due to the fact that the dimension doesn't match the format that the function monthname returns which is what Rocky is suggesting.
Try to upload the file or at least copy and paste the dimensions, expressions for the graph and how they are generated in the script.
Regards.
Dear Rocky
Sorry, I have tried your expression, but it still does not work for me.
Please find below copy of the script:
IP_FACTPAS:
LOAD
*;SQL
SELECT APCKey, DateKeyFROM tableName
WHERE
DateKey > '31 Mar 2008';
Store
IP_FACTPAS into locationDROP TABLE IP_FACTPAS;
Basically, I would like to be able to obtain something which I can obtain (in SQL) adding the piece of code :and DateKey < 01 Oct 2010' after
DateKey > '31 Mar 2008'.
I hope this makes sense. Thanks in advance.
Try adding 2 Y's because you have a year with 4 digits and not 2.
Count(if [Month Name] <> date(today(), 'MMM YYYY'), APCKey)
Dear Karl
I have tried every permutation of the above expression, without success. Thank you anyway.
Regards,
Not good. There must be something that we're not contemplating. If you want us to look at it more, try to upload a QV app with the specific example of the graph.
Regards.
I have found a solution (even if it might not be very orthodox). I have added this line to the Script:
WHERE
and it works.
Thanks guys for your help!