topic Re: If sheet filtered by month, one chart should be filtering that month -1 in App Development

If sheet filtered by month, one chart should be filtering that month -1

patriciousa — Fri, 29 Jan 2016 14:09:20 GMT

Hi community.

I have a sheet where I have 2 pie charts and a big filter pane at the top with "Month" selection.

If I hit November in the filter, both charts will filter November. I need to be able to make a comparison with the previous month.

So, if I filter November, the other chart should be showing October.

If August selecter, the other chart should be showing July, etc.

"Mes" is Month in spanish.

Thank you in advance.

Regards.

Re: If sheet filtered by month, one chart should be filtering that month -1

Gysbert_Wassenaar — Fri, 29 Jan 2016 17:28:28 GMT

Create a numeric month field in the script: num(Month(MyDate)) as MonthNum. Then try changing the expression of the 'Previous Month' chart by adding a set selector to it. For example Sum(Amount) becomes Sum({<MonthNum={"$(=Max(MonthNum)-1)"}>}Amount)

If you also need to cross year boundaries then try using a date field:

Sum({<MyDate={">=$(=MonthStart(Max(MyDate),-1))<$(=MonthStart(Max(MyDate)))"}>}Amount)

Re: If sheet filtered by month, one chart should be filtering that month -1

patriciousa — Fri, 29 Jan 2016 17:43:43 GMT

Hi Gysbert. Thank you for your help but can you be a little bit more clear? I'm new to Qlik and scripts, strings, related stuff.

Everything I have achieved so far is thanks to this community and guides.

My month filter exists thank to this script:

Calendar:

DECLARE FIELD DEFINITION TAGGED '$date'

Parameters

first_month_of_year = 1

Fields

Year($1) As Year Tagged '$year',

Month($1) as Month Tagged '$month',

Date($1) as Date Tagged ('$date', '$day'),

Week($1) as Week Tagged '$week',

Weekday($1) as Weekday Tagged '$weekday',

DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS "Fecha Registro" USING Calendar;

This is letting me filter by any month, day, week, etc.

I tried using the scripts you gave me, no errors but they are doing nothing. I believe I don't even know where is the right place to paste them....

Thank you very much again.

Kind regards.

Re: If sheet filtered by month, one chart should be filtering that month -1

Gysbert_Wassenaar — Fri, 29 Jan 2016 18:25:00 GMT

Add a line to the calendar declaration to create a new MontNum field:

Calendar:

DECLARE FIELD DEFINITION TAGGED '$date'

Parameters

first_month_of_year = 1

Fields

Year($1) As Year Tagged '$year',

Month($1) as Month Tagged '$month',

Num(Month($1)) as MonthNum Tagged '$month',

Date($1) as Date Tagged ('$date', '$day'),

Week($1) as Week Tagged '$week',

Weekday($1) as Weekday Tagged '$weekday',

DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS "Fecha Registro" USING Calendar;

Re: If sheet filtered by month, one chart should be filtering that month -1

patriciousa — Fri, 05 Feb 2016 01:25:57 GMT

Hi Gysbert.

The first Pie is Sum(Amount)

The second one, the one that is suppose to show the previous month when using the top month filter, is still showing the same results as the other pie.

The expression of the second pie is the one you told me:

Sum({<MonthNum={"$(=Max(MonthNum)-1)"}>}Amount)

Thank you for your help.

Regards.

Re: If sheet filtered by month, one chart should be filtering that month -1

Gysbert_Wassenaar — Fri, 05 Feb 2016 17:10:39 GMT

If you make a selection in the Month field then you also need to overrule that selection:

Sum({<Month=,MonthNum={"$(=Max(MonthNum)-1)"}>}Amount)