Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
robertocesp
Creator II
Creator II

How to add an specific filter on a line chart?

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?

16 Replies
Not applicable

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
MVP
MVP

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.

robertocesp
Creator II
Creator II
Author

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
MVP
MVP

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.

robertocesp
Creator II
Creator II
Author

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

jagan
MVP
MVP

Hi,

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

Regards,

Jagan.

robertocesp
Creator II
Creator II
Author

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

jagan
MVP
MVP

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.

robertocesp
Creator II
Creator II
Author

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.