topic Re: How to filter automatically a chart or table? in QlikView

How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 14:00:14 GMT

Hi,

I will like to display a chart for last 6 months, 12 months, 3 months, I have performed this manually:

=Count({$<YR_Cal={2016}, Month={">06"} >} AutoID)

Each month, I should update this expression. How can I update the expression to have the same result, each month automatically, I mean that I don't need to update the expression and it will be calculated automatically.

I have fields as YYYY-MM, Fiscal Year-Quarter (FYYYYY-Q#), created date (full date).

Thank's in advanced for your help.

Re: How to filter automatically a chart or table?

sunny_talwar — Mon, 05 Dec 2016 14:04:10 GMT

How do you change your expression each month? Add one month?

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 14:43:14 GMT

Yes, I changed from Month={">6"} to ={">7"}

Re: How to filter automatically a chart or table?

sunny_talwar — Mon, 05 Dec 2016 14:47:26 GMT

Is this based on current month or some other logic? So, since right now we are in December, would you use 12 or another number?

Re: How to filter automatically a chart or table?

Gysbert_Wassenaar — Mon, 05 Dec 2016 14:47:56 GMT

What do you want it to show? It can't show three periods (3, 6 and 12 months) at the same time.

Perhaps something like this works for you:

{<VR_Cal=,Month=,[created date]={">=$(=Date(MonthStart(Max([created date]),-5),'YYYY-MM-DD'))"}>}
Replace the date format YYYY-MM-DD with whatever date format the field [created date] has.

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 14:52:08 GMT

btw what is the field name with format "YYYY-MM" in your table

Assuming the field name with format "YYYY-MM" is MonthYear .

for six months

=Count({$<YR_Cal={2016},[MonthYear]={">=$(=Date(AddMonths(Max(MonthYear), -6), 'YYYY-MM'))"}>} AutoID)

for 12 months

=Count({$<YR_Cal={2016},[MonthYear]={">=$(=Date(AddMonths(Max(MonthYear), -12), 'YYYY-MM'))"}>} AutoID)

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 15:34:15 GMT

I have the field YRMON, this field stored the data as '2016-12' for December this year. As I cannot use it as it's in the expressions I save the values in two additional fields YR_Cal = 2016 and Month = 12 in this example.

In the expression I am displaying last 6 months, so it's:

=Count({$<YR_Cal={2016}, Month={">06"} >} AutoID)

What I want it's that always displays the last 6 months, but when the year is done this expression won't work, because month will be 01, so the second part of the expression won't take it.

How can I use my YRMON field to (YYYY-MM) in those conditions?

Please advise.

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 15:34:54 GMT

No, I will show the periods in three different charts, but the logic to automatic display last 3, 6 or 12 months will be the same.

Re: How to filter automatically a chart or table?

sunny_talwar — Mon, 05 Dec 2016 15:38:56 GMT

May be like this:

If you YRMON field is not read as date

=Count({$<AutoID = {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -6)"}>} AutoID)

If YRMON is read as date

=Count({$<YRMON= {"$(='>' & Date(MonthStart(Today(), -6), 'YYYY-MM'))"}>} AutoID)

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 16:01:27 GMT

Thank you, this worked, how can I convert YRMON as date?

Re: How to filter automatically a chart or table?

sunny_talwar — Mon, 05 Dec 2016 16:03:43 GMT

In the script, you can do like this

LOAD Date(MonthStart(Date#(YRMON, 'YYYY-MM')), 'YYYY-MM') as YRMON

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 16:06:58 GMT

How can I do something similar but by quarters, having the FYQTR with values as F2016-Q1?

Re: How to filter automatically a chart or table?

sunny_talwar — Mon, 05 Dec 2016 16:16:02 GMT

Do what exactly? use it to create last 6 months or make it read as a date in QlikView?

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 16:23:11 GMT

I have the following expression:

=Count({$<YR={2017}, QTR={">=01"} >} AutoID)

In this example I stored the month and quarter in two different fields.

What I need is that always display the latest one, and in another chart the last 4 of them, the field is called FYQTR and the values are FY2016-Q1.

I have tried something like this to display the latest one:

=Count({1< FYQTR = {"$(=MaxString(FYQTR))"}>} AutoID)

But it's not working.

Re: How to filter automatically a chart or table?

sunny_talwar — Mon, 05 Dec 2016 16:26:06 GMT

You only need the most recent fiscal quarter in this new chart?

Re: How to filter automatically a chart or table?

Anonymous — Mon, 05 Dec 2016 16:29:11 GMT

I need on chart for the most recent fiscal quarter and another chart for the last 4

Re: How to filter automatically a chart or table?

sunny_talwar — Tue, 06 Dec 2016 02:23:01 GMT

I would use your date fields instead of quarter fields to get last 1 quarter. The reason I say is that is because dates in set analysis is already a little complicated and adding fiscal year field (which is most likely a text field) can complicate things. I would suggest you to look at this thread:

Dates in Set Analysis‌

If you still continue to have issue, I would suggest you to share a sample so that one of us can help you better.

Best,

Sunny

Re: How to filter automatically a chart or table?

Anonymous — Tue, 06 Dec 2016 12:10:46 GMT

I will take a look and if I have problems I will open a new post. Can you please tell me which would be the best way to call the highest FYQTR value as the =Count({1< FYQTR = {"$(=MaxString(FYQTR))"}>} AutoID) is not working?

Re: How to filter automatically a chart or table?

sunny_talwar — Tue, 06 Dec 2016 12:18:48 GMT

This should have worked... can you check what you get when you just use this in a text box object

=MaxString(FYQTR)

Re: How to filter automatically a chart or table?

Anonymous — Tue, 06 Dec 2016 12:22:22 GMT

No data to display is the result of that.