topic Restricting month rows to a range in App Development

Restricting month rows to a range

qliksensenoob — Tue, 25 May 2021 09:23:41 GMT

Hi,

I'm new to Qliksense and have a problem i've been trying to find a solution on.

I am able to create a table such as the below which is pulled from an excel sheet to show in the visualisation.

Month	A	B	C	Remarks
Apr-19	94839	552
May-19	102694	956
Jun-19	100505	1757
Jul-19	92242	509
Aug-19	94082	338
Sep-19	77458	256
Oct-19	118299	281
Nov-19	110946	264
Dec-19	8871	93
Jan-20	14265	266
Feb-20	70204	256	82060
Mar-20	88686	395	82455
Apr-20	148056	755	83210
May-20	65484	2263	85473
Jun-20	31624	2200	87673	Remarks are here
Jul-20	57231	288	87961
Aug-20	57919	316	88277
Sep-20	61008	467	88744
Oct-20	74748	451	89195
Nov-20	72549	500	89695

However, i have 2 different sheets, one for FY2020 (Apr 2020-May 2021) and the other for FY2019 (Apr 2019-May 2020). I would like each sheet to restrict to the relevant rows that are shown. So for the first sheet FY2019 , i want it to show months Apr 2019 - May 2020 only such as the below. In fact, i have created the variable FY2019 to be ">=Apr-19<=Mar-20" and hoped to use this in the visualisation somewhere but can't figure out where or how.

In the visualisation i've added the columns as dimensions but not sure how i can further filter them to how i want them. Do help.

Month	A	B	C	Remarks
Apr-19	94839	552
May-19	102694	956
Jun-19	100505	1757
Jul-19	92242	509
Aug-19	94082	338
Sep-19	77458	256
Oct-19	118299	281
Nov-19	110946	264
Dec-19	8871	93
Jan-20	14265	266
Feb-20	70204	256	82060
Mar-20	88686	395	82455
Apr-20	148056	755	83210
May-20	65484	2263	85473

Re: Restricting month rows to a range

jwjackso — Tue, 25 May 2021 10:58:08 GMT

Define the Month dimension as If(Month>='19-Apr' and Month<='20.Mar',Month,Null()) and then suppress Nulls on the dimension.

Re: Restricting month rows to a range

qliksensenoob — Wed, 16 Jun 2021 01:58:54 GMT

Thanks for the solution. Just a simple follow-up question.

Why does Month>='19-Apr' work instead of Month>='Apr-19'?

Re: Restricting month rows to a range

jwjackso — Wed, 16 Jun 2021 13:41:16 GMT

Whenever I have problems with dates, I put them in a table or text box to see how the date is formatted. In the table that you displayed, the Month field looks like 19-Apr. You will need the IF test comparison to match the way the data is formatted.

Re: Restricting month rows to a range

qliksensenoob — Mon, 21 Jun 2021 01:46:24 GMT

i notice when i copy the table to excel, the dates are formatted as 19-Apr which is what you see. Is that the way to check on how dates are formatted? If not, can you advise how you do it?

Re: Restricting month rows to a range

jwjackso — Mon, 21 Jun 2021 13:38:03 GMT

Load the table in Qlik Sense and then add the date field to a straight table. This should show you the default format of the date.

Re: Restricting month rows to a range

qliksensenoob — Tue, 13 Jul 2021 09:23:15 GMT

I'm so sorry to ask you this but i don't quite understand what you mean. I already loaded the table in qliksense after uploading an excel file but what i see is the below. Can i understand how you do it?

Re: Restricting month rows to a range

jwjackso — Tue, 13 Jul 2021 11:42:54 GMT

It looks like MonthTest is formatted as D/M/YYYY. I suspect that the display format for Month is MMM-DD, but the actual data format is DD-MMM.

Re: Restricting month rows to a range

qliksensenoob — Wed, 14 Jul 2021 01:52:34 GMT

ok so is there a way to find out the actual data format? Are you able to share the steps?