Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

patriciousa
Contributor

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

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.

Untitled.png

Thank you in advance.

Regards.

5 Replies

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

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)


talk is cheap, supply exceeds demand
patriciousa
Contributor

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

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

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;


talk is cheap, supply exceeds demand
patriciousa
Contributor

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

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

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

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


talk is cheap, supply exceeds demand