Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have a file with Acount, Type, Start Date and End Date.
It should calculate the number of any date, how many accounts fall within the start date and end date.
Count only accounts with type = 1 and accounts, which start with 2.
I have shown in the appendix for better understanding and the result.
The Dimmension is the month of the selected year. It is always calculated on a key date because the file changes every month
Thank you for your help in advance
Hi,
one solution might be:
table1:
LOAD acount,
typ,
Date_start,
Date_end
FROM [https://community.qlik.com/servlet/JiveServlet/download/1374887-302367/test3.xls] (biff, embedded labels, table is Sheet1$, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 28)),Remove(Row, Pos(Top, 27)),Remove(Row, Pos(Top, 26)),Remove(Row, Pos(Top, 25)),Remove(Row, Pos(Top, 24)),Remove(Row, Pos(Top, 23)),Remove(Row, Pos(Top, 22)),Remove(Row, Pos(Top, 21)),Remove(Row, Pos(Top, 20)),Remove(Row, Pos(Top, 19)),Remove(Row, Pos(Top, 18)),Remove(Row, Pos(Top, 17)),Remove(Row, Pos(Top, 16)),Remove(Row, Pos(Top, 15))));
tabDateLink:
LOAD acount,
Date(Date_start+IterNo()-1) as Date
Resident table1
While Date_start+IterNo()-1 <= Date_end;
tabCalendar:
LOAD *,
Day(Date) as Day,
WeekDay(Date) as WeekDay,
Week(Date) as Week,
WeekName(Date) as WeekName,
Month(Date) as Month,
MonthName(Date) as MonthName,
Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
QuarterName(Date) as QuarterName,
Year(Date) as Year,
WeekYear(Date) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD MakeDate(2015) as MinDate,
MakeDate(2020)-1 as MaxDate
AutoGenerate 1;
Right Join (tabDateLink) LOAD Date Resident tabCalendar;
hope this helps
regards
Marco