topic Re: Custom calendar in QlikView

Custom calendar

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have sales on daily basis for a complete year.

I Want to show the weekly sales. But User will define his own canlendar i.e., week start and weekend.

this calendar will not be constant that can change on monthly basis.

EX: user calendar

october:

week no	week start	week end
Week 1	1/10/2018	5/10/2018
Week 2	6/10/2018	12/10/2018
Week 3	13/10/2018	19/10/2018
Week 4	20/10/2018	26/10/2018
Week 5	27/10/2018	31/10/2018

‌

november:

week no	week start	week end
Week 1	1/11/2018	2/11/2018
Week 2	3/11/2018	9/11/2018
Week 3	10/11/2018	16/11/2018
Week 4	17/11/2018	23/11/2018
Week 5	24/11/2018	30/11/2018

Re: Custom calendar

viveksingh — Fri, 26 Oct 2018 07:28:19 GMT

stalwar1‌, gwassenaar‌ can you help me on this please..

Re: Custom calendar

jensmunnichs — Fri, 26 Oct 2018 07:41:39 GMT

Need some more information I think.

- Is the calendar actually user defined (as in, they input their own week start and week end values), or does it always follow the logic of your example here? (The logic I see is every week 1 starts on the first day of the month, every other week starts on saturday, and every last week of the month ends on the last day of the month, every other week ends on friday)

- If it is user defined, is the user allowed to reload?

- Does every month start back at week 1?

Re: Custom calendar

viveksingh — Fri, 26 Oct 2018 08:05:42 GMT

‌yes, it is user defined one. In one month They take month start as 1st day of month. But there may be change like in another month, the week start will be 2nd date or any another of the month

yes, every month starts with week 1. But in a week there may be 1day or 5days or 7 Days...

one more point is, week numbers also may change. In this month we have 4 weeks. But in another month we may have 7 weeks.

user will define them in a excel, will be reloaded on monthly basis

Re: Custom calendar

jensmunnichs — Fri, 26 Oct 2018 08:26:03 GMT

Aha, in that case this might be a lot simpler than I initially thought. Have a look at the load prefix 'IntervalMatch':

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/IntervalMatch.htm

So basically, you want to load in your data (Date, Sales, etc.) and the excel file with fields (week start, week end, week no). Then use IntervalMatch(Date) just like they did in the example on that page, and then, if done correctly, every record should have an extra field 'Week No' with Week 1, week 2, etc. which you can then use in your tables/charts.

Every time the excel sheet gets updated and the app is reloaded, the week no's will change accordingly.

Hope this helps

Re: Custom calendar

Anil_Babu_Samineni — Fri, 26 Oct 2018 09:18:19 GMT

What is the question? Am i missed anything above message?