How to add an specific filter on a line chart?

[Anonymous]

Hi,

I am creating a Line Chart on QlikView, I am using a field that will have the date as the following: 2016-09, 2016-08, ..., 2014-01

The problem is that I will like to display on the chart only the last 6 months, for this I am trying to add a calculated dimension for this field, but it's not working.

When I used just the field, all months are being displayed from the last 2 years, I tried to add an additional filter as =YearMonth >'2016-02', but is not working, if I removed the field and leave only this calculated dimension the line chart only displays one value.

What can I do to fix this?

[#2]

Hello Roberto,

Not sure how you are loading data, here is an example of resolution:

First data load (I am making sure that field is laoded as date)

LOAD
Date#(Date,'YYYY-MM') as Date,
Value
;
LOAD * INLINE [
    Date, Value
    2016-10, 1
    2016-09, 1
    2016-08, 2
    2016-07, 3
    2016-06, 1
    2016-05, 6
    2016-04, 7
    2016-03, 9
    2016-02, 3
    2016-01, 4
];

Then I am adding calculated expression in chart:

=IF(Date>=AddMonths(MonthStart(Today(1)),-6), Date)

Also marking "suppress when value is null"

You can also play around with "Dimensions Limits" tab.

Adding sample file for you.

BR,

Kuba

[jagan]

Hi,

First convert your month field to a valid date format in script like below

DAta:

LOAD

*,

MonthName(DateFieldName) AS Month;

LOAD

Date(Date#(DateFieldName, 'YYYY-MM')) AS DateFieldName,

Value

FROM DataSource;

Now in set analysis try like below

Last 6 Months Sales

Sum({<DateFieldName={‘>=$(=MonthStart(Max(DateFieldName), -5))<=$(=Date(Max(DateFieldName)))’}>} Sales )

Check below link for similar kind of expressions for various scenarios.

Set Analysis for Rolling Periods

Regards,

Jagan.

[Anonymous]

Hi,

I don't have problems with the field, as it's already with values as 'yyyy-mm' what I need is to create a line chart for last 6 months, and here is where I have problems. As I will like to have this filter only for one dashboard I am not being able to create it.

Is there a way to create a filter for this specific request. I tried in the a calculated dimensions, or with the limits, but still not able.

I'll appreciated your help

[jagan]

Hi,

I think the Month is in the string format, so you have to convert to date to apply filters in set analysis.

Regards,

Jagan.

[Anonymous]

Then, how can I convert that, when the data is already imported and used in another sections?

[jagan]

Hi,

Convert the field in script and derive a new column, and use this new column in ur set analysis.

Regards,

Jagan.

[Anonymous]

Sure, I will do that, can you please help me with the steps to convert the field?

[jagan]

Hi,

Try this

DAta:

LOAD

*,

MonthName(DateFieldName) AS Month;

LOAD

Date(Date#(DateFieldName, 'YYYY-MM')) AS DateFieldName,

Value

FROM DataSource;

Now in set analysis try like below

Last 6 Months Sales

Sum({<DateFieldName={‘>=$(=MonthStart(Max(DateFieldName), -5))<=$(=Date(Max(DateFieldName)))’}>} Sales )

Check below link for similar kind of expressions for various scenarios.

Set Analysis for Rolling Periods

Regards,

Jagan.

[Anonymous]

I have tried the load in the script section but it failed. I have tried the following:

Sum({<DateFieldName={‘>=$(=MonthStart(Max(DateFieldName), -5))<=$(=Date(Max(DateFieldName)))’}>} Sales )

In the DateFieldName I used the YRMON field that already has the 'YYYY-MM' data, but don't know what "Sales" means or where comes from, so I was not able to use it.

I have create filters that are being set automatically when I hit on a text box, something like this:

The dashboards are being filtered by this, when I hit on the text box. This is how the properties of the dimensions look like:

Now, I have try the same dimension on the specific dashboard, but it's not doing anything. Is there any way to create a filter in each dashboard that won't affect other ones? that is what I am looking for. Something similar to the second picture but that works.

Thank you in advanced for your help.