topic Exclude the current month from a chart in QlikView

Exclude the current month from a chart

Fri, 08 Oct 2010 16:25:43 GMT

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.

Exclude the current month from a chart

pover — Fri, 08 Oct 2010 16:38:06 GMT

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.

Exclude the current month from a chart

Fri, 08 Oct 2010 17:09:35 GMT

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.

Exclude the current month from a chart

boorgura — Fri, 08 Oct 2010 17:13:55 GMT

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.

Exclude the current month from a chart

pover — Fri, 08 Oct 2010 17:38:13 GMT

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.

Exclude the current month from a chart

Mon, 11 Oct 2010 17:17:00 GMT

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, DateKey

FROM tableName

WHERE

DateKey > '31 Mar 2008';

Store

IP_FACTPAS into location

DROP 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.

Exclude the current month from a chart

pover — Mon, 11 Oct 2010 17:38:27 GMT

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)

Exclude the current month from a chart

Tue, 12 Oct 2010 12:00:31 GMT

Dear Karl

I have tried every permutation of the above expression, without success. Thank you anyway.

Regards,

Exclude the current month from a chart

pover — Tue, 12 Oct 2010 17:20:50 GMT

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.

Exclude the current month from a chart

Wed, 13 Oct 2010 15:08:25 GMT

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!

DateKey < MonthStart(today());