Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
anwarbham
Contributor III
Contributor III
‎2020-11-24 10:11 AM

limiting dimension values of month by start date and end date

Hi i wonder if anyone can help me find a solution for a  tricky problem.

 

i have a fact table that Holds a %Calendar_Key and a %Cycle_Plan_Key and a %Consent_Key

the problem is the %Calendar_Key is a range calender key that holds start date and an end date.

This is to determine when a Consent started and when it will expire.

eg %Consent_Key = 123456 has a calender key of  43131|44159  this equates to the below

31/01/2019
until 

31/10/2020


this will join to a master calender that holds all the individual months and years for this range. 

on the other side i have also a list of %Cycle_Plan_Key in the fact table this will determine which monthly cycles fall withing the start date and end date range, 

example. 

%Cycle_Plan_Key

a4g1o000000TVeHAAW
Cycle name is 

2019.1.1-2019.4.30


the problem is when i select this Cycle Name 2019.1.1-2019.4.30 i get all the month from jan until december , instead of just the months that fall into this Cycle. whcih should be Jan to April.

in the Cycle Dimension i do have a start date and end date that i can use to limit the dimension in the calender but im unsure how. 

Cycle End Date Cycle Start Date
30/04/2019 01/01/2019


Fact

%Cycle_Plan_Key%Account_Key%Calendar_KeyCalenderKeyStartCalenderKeyEndYear Month%Consent_KeyCycle NameMonth
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415931/01/201824/11/202001/10/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R110
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415901/02/201824/11/202001/09/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R19
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415902/02/201824/11/202001/08/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R18
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415903/02/201824/11/202001/07/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R17
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415904/02/201824/11/202001/06/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R16
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415905/02/201824/11/202001/05/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R15
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415906/02/201824/11/202001/04/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R14
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415907/02/201824/11/202001/03/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R13
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415908/02/201824/11/202001/02/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R12
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415909/02/201824/11/202001/01/2020a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R11
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415910/02/201824/11/202001/12/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R112
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415911/02/201824/11/202001/11/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R111
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415912/02/201824/11/202001/10/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R110
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415913/02/201824/11/202001/09/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R19
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415914/02/201824/11/202001/08/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R18
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415915/02/201824/11/202001/07/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R17
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415916/02/201824/11/202001/06/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R16
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415917/02/201824/11/202001/05/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R15
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415918/02/201824/11/202001/04/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R14
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415919/02/201824/11/202001/03/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R13
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415920/02/201824/11/202001/02/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R12
a4g1o000000TVeHAAW001U000000mH6WhIAK43131|4415921/02/201824/11/202001/01/2019a3R1o000000cFC2EAM2019.1.1-2019.4.30-ES-R11


Cycle Dimension

Cycle Id%Cycle_Plan_KeyCycle End DateCycle Start DateCycle Plan IdCycle Plan NameCycleCycle Name
a4i1o000000UnIKAA0a4g1o000000TVeHAAW30/04/201901/01/2019a4g1o000000TVeHAAW2019.1.1-2019.4.30-ES-ES_3604_CASSpain 01/01/2019 - 30/04/20192019.1.1-2019.4.30-ES-R1

 

 

Master Calender

%Calendar_KeyDateDate NumMonthQuarterCycleNoYearYear MonthYear Month NumTimeframe DisplayedYear Cycle
43131|4415931/01/201931/01/2019111201901/01/201901/01/2019MonthlyC1-2019
43131|4415928/02/201928/02/2019211201901/02/201901/02/2019MonthlyC1-2019
43131|4415931/03/201931/03/2019311201901/03/201901/03/2019MonthlyC1-2019
43131|4415930/04/201930/04/2019421201901/04/201901/04/2019MonthlyC1-2019
43131|4415931/05/201931/05/2019522201901/05/201901/05/2019MonthlyC2-2019
43131|4415930/06/201930/06/2019622201901/06/201901/06/2019MonthlyC2-2019
43131|4415931/07/201931/07/2019732201901/07/201901/07/2019MonthlyC2-2019
43131|4415931/08/201931/08/2019832201901/08/201901/08/2019MonthlyC2-2019
43131|4415930/09/201930/09/2019933201901/09/201901/09/2019MonthlyC3-2019
43131|4415931/10/201931/10/20191043201901/10/201901/10/2019MonthlyC3-2019
43131|4415930/11/201930/11/20191143201901/11/201901/11/2019MonthlyC3-2019
43131|4415931/12/201931/12/20191243201901/12/201901/12/2019MonthlyC3-2019
43131|4415931/01/202031/01/2020111202001/01/202001/01/2020MonthlyC1-2020
43131|4415929/02/202029/02/2020211202001/02/202001/02/2020MonthlyC1-2020
43131|4415931/03/202031/03/2020311202001/03/202001/03/2020MonthlyC1-2020
43131|4415930/04/202030/04/2020421202001/04/202001/04/2020MonthlyC1-2020
43131|4415931/05/202031/05/2020522202001/05/202001/05/2020MonthlyC2-2020
43131|4415930/06/202030/06/2020622202001/06/202001/06/2020MonthlyC2-2020
43131|4415931/07/202031/07/2020732202001/07/202001/07/2020MonthlyC2-2020
43131|4415931/08/202031/08/2020832202001/08/202001/08/2020MonthlyC2-2020
43131|4415930/09/202030/09/2020933202001/09/202001/09/2020MonthlyC3-2020
43131|4415931/10/202031/10/20201043202001/10/202001/10/2020MonthlyC3-2020


At the moment this is what im getting 

 

Labels (2)
Labels
Preview file
22 KB
Preview file
11 KB
1,563 Views
0 Likes
1 Solution

Accepted Solutions
anwarbham
Contributor III
Contributor III
‎2020-11-24 01:20 PM
Author

I managed to fix this 

with the following  formula in  the month dimension and i get the dimension month correctly 

if(Month([Cycle Start Date]) = 'May' and 
Match (Month, 'May','Jun','Jul', 'Aug'), Month,
if(Month([Cycle Start Date]) = 'Jan' and 
Match (Month, 'Jan','Feb','Mar', 'Apr'), Month,
if(Month([Cycle Start Date]) = 'Sep' and 
Match (Month, 'Sep','Oct','Nov', 'Dec'), Month
)
)
)

 

View solution in original post

1,525 Views
0 Likes
2 Replies
anwarbham
Contributor III
Contributor III
‎2020-11-24 12:38 PM
Author

what i want.pngwhat i want 

 

what i have.png  what i have 

1,532 Views
0 Likes
anwarbham
Contributor III
Contributor III
‎2020-11-24 01:20 PM
Author

I managed to fix this 

with the following  formula in  the month dimension and i get the dimension month correctly 

if(Month([Cycle Start Date]) = 'May' and 
Match (Month, 'May','Jun','Jul', 'Aug'), Month,
if(Month([Cycle Start Date]) = 'Jan' and 
Match (Month, 'Jan','Feb','Mar', 'Apr'), Month,
if(Month([Cycle Start Date]) = 'Sep' and 
Match (Month, 'Sep','Oct','Nov', 'Dec'), Month
)
)
)

 

1,526 Views
0 Likes