topic Re: Filter data based on Day for every Month in QlikView

Filter data based on Day for every Month

sumeet-vaidya — Wed, 22 Aug 2018 09:29:52 GMT

Dear all,

I have a scenario wherein I want my report to be filtered based on the day selected.

Eg. Please find below report format.

Case 1:

When User selects Day = 5,

Data for APR => 01-APR-2018 to 05-APR-2018

Data for MAY=> 01-MAY-2018 to 05-MAY-2018

Data for JUN=> 01-JUN-2018 to 05-JUN-2018 and so on would be filtered.

Channel	Month	APR	MAY	JUN	JUL	AUG	TOTAL
A		25	45	85	69	85	309
B		15	36	45	78	98	272
C		12	10	15	14	45	96
D		35	45	68	78	58	284
E		36	45	89	69	58	297
TOTAL		123	181	302	308	344	1258

Please assist.

stalwar1‌ hic‌

Thanks & Regards

Sumeet

Re: Filter data based on Day for every Month

timpoismans — Wed, 22 Aug 2018 09:39:55 GMT

In my opinion, using a Calendar table would work the best.

Most known Calendar tables provide Day, Week, Month, Quarter, Year fields for the dates.

Users could then select Day 1 to Day 5 and perhaps the year, in case of multiple years, without selecting any month or quarter. This would limit the data shown to Day 1 to Day 5 for every month that's available in the selected year, or all time if not selected any.

Re: Filter data based on Day for every Month

sumeet-vaidya — Wed, 22 Aug 2018 09:52:43 GMT

Dear Tim,

Calendar is used already. There is date field in table where user requires day selection and as per that day data needs to be filtered for every month.

Regards

Sumeet

Re: Filter data based on Day for every Month

timpoismans — Wed, 22 Aug 2018 09:57:32 GMT

Perhaps I'm misunderstanding this, but if you are already using a Calendar, there shouldn't be an issue?

Include a selection-object on the [Day]-field of your Calendar on the views that need day selections and users can select 1->5, 7->10, 4->27, any range they want.

As long as they don't select a month, data will be limited to the selected days.

Re: Filter data based on Day for every Month

ajaykumar1 — Wed, 22 Aug 2018 10:03:37 GMT

Try Like this;

If (Monthstart(date((YourDateFiled)>= 1 and monthstart(date(YourDateFiled)<=5,'1 To 5',

If (Monthstart(date((YourDateFiled)>= 6 and monthstart(date(YourDateFiled)<=10,'6 To 10'

If (Monthstart(date((YourDateFiled)>= 11 and monthstart(date(YourDateFiled)<=16,'11 To 16'

If (Monthstart(date((YourDateFiled)>= 17 and monthstart(date(YourDateFiled)<=22,'17 To 23'

If (Monthstart(date((YourDateFiled)>= 23 and monthstart(date(YourDateFiled)<=28,'23 To 28','Remianing'))))) as Month

Re: Filter data based on Day for every Month

sumeet-vaidya — Wed, 22 Aug 2018 11:09:01 GMT

Hi Ajay,

I need to write in expression as Channel & Month are my dimensions.

Regards

Sumeet