Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
robertocesp
Contributor II

How to filter automatically a chart or table?

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.

Tags (1)
1 Solution

Accepted Solutions

Re: How to filter automatically a chart or table?

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)

View solution in original post

26 Replies

Re: How to filter automatically a chart or table?

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

robertocesp
Contributor II

Re: How to filter automatically a chart or table?

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

Re: How to filter automatically a chart or table?

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?

MVP & Luminary
MVP & Luminary

Re: How to filter automatically a chart or table?

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.


talk is cheap, supply exceeds demand
galax_allu
Valued Contributor

Re: How to filter automatically a chart or table?

     


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)

robertocesp
Contributor II

Re: How to filter automatically a chart or table?

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.

robertocesp
Contributor II

Re: How to filter automatically a chart or table?

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?

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)

View solution in original post

robertocesp
Contributor II

Re: How to filter automatically a chart or table?

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