topic Re: How to add an specific filter on a line chart? in QlikView

How to add an specific filter on a line chart?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: How to add an specific filter on a line chart?

Fri, 23 Sep 2016 16:51:54 GMT

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

Re: How to add an specific filter on a line chart?

jagan — Fri, 23 Sep 2016 17:32:48 GMT

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.

Re: How to add an specific filter on a line chart?

Anonymous — Fri, 23 Sep 2016 17:57:04 GMT

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

Re: How to add an specific filter on a line chart?

jagan — Sat, 24 Sep 2016 16:38:48 GMT

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.

Re: How to add an specific filter on a line chart?

Anonymous — Mon, 26 Sep 2016 12:59:18 GMT

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

Re: How to add an specific filter on a line chart?

jagan — Tue, 27 Sep 2016 02:40:29 GMT

Hi,

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

Regards,

Jagan.

Re: How to add an specific filter on a line chart?

Anonymous — Tue, 27 Sep 2016 13:10:28 GMT

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

Re: How to add an specific filter on a line chart?

jagan — Wed, 28 Sep 2016 02:53:27 GMT

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.

Re: How to add an specific filter on a line chart?

Anonymous — Thu, 29 Sep 2016 15:06:54 GMT

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.

Re: How to add an specific filter on a line chart?

Anonymous — Mon, 03 Oct 2016 13:13:13 GMT

I have also tried something like this = count({<[YRMON]<{'2016-03'}>} [AutoID]) in the expressions, but the dashboard got a big X it's not showing the data that I need.

Re: How to add an specific filter on a line chart?

jagan — Tue, 04 Oct 2016 02:41:25 GMT

Hi,

If you can attach some sample file then it would be easier to understand and give the solution.

Regards,

Jagan.

Re: How to add an specific filter on a line chart?

Anonymous — Tue, 04 Oct 2016 12:18:30 GMT

I cannot attached an example, but I will try to be clear on my explanation. I have 3 fields ID, YRMON and Region, ID is the unique identification number, YRMON is the year and month when the ID was open and the region will be ASIA, AMERICA or EUROPE. Then I added a line chart

This char will display all the YRMON, the 3 regions and the quantity of records per month. Using as dimensions YRMON and Region, and as expression the count of IDs.

What I would like to have is that the chart only displays the last 6 YRMON. I have tried adding the above conditions as dimensions or = count({<[YRMON]<{'2016-03'}>} [AutoID]) as expression. But didn't work.

Any idea on how to do this?

Re: How to add an specific filter on a line chart?

Anil_Babu_Samineni — Tue, 04 Oct 2016 12:56:45 GMT

From this graph what area you expecting

Try this

count({<[YRMON] = {'<' & '2016-03'}>}[AutoID])

Re: How to add an specific filter on a line chart?

Anonymous — Tue, 04 Oct 2016 13:38:22 GMT

Didn't work. I got a big X on the chart:

Re: How to add an specific filter on a line chart?

Anonymous — Thu, 06 Oct 2016 16:06:56 GMT

This is what I did to fix this problem.

First added 2 fields in the script:

LOAD *, Mid(YRMON,1,4) as YR_Cal,Mid(YRMON,6,2) as Month;

Then I used them in the expression filter as the following:

=Count({1<YR_Cal={2016}, Month={">03"} >} AutoID)

The filter is working correctly. thank you all for your effort.

Re: How to add an specific filter on a line chart?

jagan — Fri, 07 Oct 2016 02:48:01 GMT

Good. close this link by giving Correct and Helpful answers to the posts which helps you.

Regards,

Jagan.