Solved: Re: In between counts for dates - Page 3 - Qlik Community

MK9885 · ‎2017-06-14

Hello,

I've a requirement to count a record by range based.

Ex:

Fact:

EMP StartDate EMPname Status

01 02/01/2014 ABC Available

01 03/01/2017 BCD Offline

In above scenario the fact has 2 entry for EMP01 for 02/2014 and 03/2017

So if I do counts it will come as 2.

But he is available through out 02/2014 till 03/2017.

So if I select 01/2015 or 05/2016 he should still be counted in that month.

My Fact is linked to master based on Datekey which is 20140201 and 20170301

I need to calculate based on range and also count him from 2014, 2015, 2016 and 2017 till March 01.

If I select any year or any month in between 2014 and 2017 I should get an entry, which I'm not getting now.

Can someone please help me calculating range based?

stalwar1‌ neelamsaroha1575‌

Thanks.

neelamsaroha157 · ‎2017-06-15

If it works then you can play around creating link table if you wanna join EMPID as well and I have hard coded initial values so you can change that as well.

Attaching modified version.

I removed last 4 records from your test data so that ENDDate is part of 2017 & not 9999 and also generated data till 2019 so that you can validate the results.

MK9885 · ‎2017-06-15

Hi Neelam,

I'm so grateful for what you've did so far....

It still doesn't seem to get what I want.

Maybe I'll put it in easy way.

I have Dates in Fact, I want to add missing dates to the fact so that emp have entry for 2014 and 2017 should be counted for 2014, 2015, 2016 and 2017.

In my chart I'm taking Month end calculation for employee.

so If I select EMP 01 for month of Aug, he should come same for other months as we'll be inserting all missing dates for that year.

It might be confusing but Start Date and End Date are in way not valid.

cus a record entry would be added into fact and we need to fetch in between dates for that EMP.

I'm also trying this since yesterday and tried many methods but none seem to work.

Bottom line is inserting missing dates into the fact and mapping it to employee. If any month/year/quarter/day is selected he should be counted based on fact dates.

We need to neglect StartDate and End date from other table.

Just Fact table is enough plus a master calendar I guess.

Please help if you can....please find the new modified xl which has more than 1 emp to verify, can neglect EMP Table.

Thanks.

neelamsaroha157 · ‎2017-06-15

No Attachment

MK9885 · ‎2017-06-15

Added attachment....

Thanks.

neelamsaroha157 · ‎2017-06-15

Check this.

Made changes to your excel because it had StartDate > ENDdate (I just Reversed them).

MK9885 · ‎2017-06-15

Thanks a lot...

It's working, will implement this into my original data model and see how it works...

But I guess I'm not sure cus I've many Dim tables with Start and end Date, that is why I just wanted to add entries to Fact Dates but totally ignore Emp table.

but let's see if I can adjust this script into mine.

And will reply here if any doubts?

thanks again.

neelamsaroha157 · ‎2017-06-15

I am glad it is working.

You can have as many Dim tables you want with Start & End Date.

All you have to do is to use Interval match for every Start & End Date with the master calendar and add the key of the table to Link table.

MK9885 · ‎2017-06-15

Ok, thanks good to know.

Will try it and let you know...

My tables are jumbled and I need to adjust it as per your new script.

I already have a master calendar with Tempdate defined, need to adjust that also.. let's see how well I can do it after all the efforts from you.

thanks.

neelamsaroha157 · ‎2017-06-15

You might not need to change your master calendar. Use 'tempdate' from your calendar for the interval match instead of 'Date' from my script.

MK9885 · ‎2017-06-15

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

    MAPPING LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3) as Quarter



    AUTOGENERATE (12);



       varMinDate = num(date(mid('2011-01-01',1,10 ),'YYYY-MM-DD'));

    varMaxDate = num(date(mid('2017-05-23',1,10 ),'YYYY-MM-DD'));



//       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar



TempCalendar:

    LOAD

                   $(varMinDate) + Iterno()-1 as Num,

                   Date($(varMinDate) + IterNo() - 1) as TempDate

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);



// Date Dimension

MasterCalendar:

LOAD

*,

If (Year >= $(vToday5Year), Year) as [Year List],

Num_Month-1 as LastMonthNum,

Day([Calendar Month End Date]) as EOD,

if(Month(MakeDate(Year,Month,1)-1),1,0) as PreviousMonth,

[Quarter Number]-1 as PQ,

if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,

Month(Num_Month) as MONTHNAMES,

    WEEKZENDS&'- '&[Effective Date] as QMix,

    if([Calendar Month End Date]>=0,1,0) as EndFlag,

AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load



trim(date(TempDate,'YYYYMMDD')) as DATEKEY,

date(TempDate,'MM/DD/YYYY') as [Effective Date],

if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

if((MonthEnd(TempDate)),1,0) as [LastDay123],

Floor(TempDate) as DateNum,

WeekEnd(TempDate) as WEEKZENDS,

MakeDate(2016,48) as WEEKSA,

if(Week(WeekEnd(TempDate))=Week(TempDate),1,0) as WWEKS,

Week(date(TempDate,'MM/DD/YYYY')) as WEEEEKS,

WeekStart(TempDate)as WKS,

WeekEnd(TempDate) as WKE,

Date(TempDate-1,'MM/DD/YYYY') as LastDay2,

      MonthStart(date(TempDate,'DD/MM/YYYY')) as DS,

    day(TempDate) as Day,

    InDay(TempDate, today(),-22)*-1 as Day1,

    Day(TempDate-1) as LastDay,

    TempDate as [US Calendar Format],

    date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],

    date(TempDate,'WWWW') as [Full Day Name],

    year(TempDate) as Year,

    inyear(TempDate,today(),0) * -1   as [CY],    // Current Year

    inyear(TempDate,today(),-1) * -1 as [First PY],

    inyear(TempDate,today(),-2) * -1 as [Second PY],

    inyeartodate(TempDate,today(),0) * -1   as [CMTD],

    inmonthtodate(TempDate,today(),0) * -1   as [CYTD],

    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

      if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,

if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,

if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

if(InQuarterToDate(TempDate, today(),-3), 1, 0) as CurQtr1Flag,

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InQuarter(TempDate, today(),+2), 1, 0) as LastttttQtrFlag,

if(InQuarter(TempDate, today(),-4), 1, 0) as PQtrFlag,

if(InMonth(TempDate, today(),-1), 1, 0) as LastMonthFlag,

if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQRTRLastMonthFlag,

if(num(weekday(TempDate))=5 or num(weekday(TempDate))=6,1,0) as WeekFlag,

if(InWeekToDate(TempDate, today(),-2), 1, 0) as LastWeekLastQuarterFlag,

if(InWeek(TempDate,today(),-1),1,0) as wwe,



    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,



    Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],



    inquarter(TempDate,today(),0) * -1 as [CQ], // Current Quarter

    inquarter(TempDate,today(),-1) * -1 as [AQ],

     inquarter(TempDate,today(),-4) * -1 as [First PQ],

    inquarter(TempDate,today(),-8) * -1 as [Second PQ],

    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

    date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

    If( TempDate > monthstart(addmonths(today(),-3)) and TempDate <= Today(),1) as [Rolling 3]



Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table QuarterNAME;

This is my Master Calendar Script and the tables are in order of

EMP Master Fact

To add your script, what needs to be adjusted? Is that a correct order?

Cus in your script we've Fact in first tab, then calendar and then emp

But I cannot keep Emp last as I'm doing a left join to fact using EMP table, it has to come before Fact