Include years between Start Date and End Date

[Anonymous]

Hi Team,

I have a Table stating student details.

Student 1d, Student  Name, Start Date, End Date

   1,A,'2012-04-01','2016-12-01'

   2,B,'2017-01-03','2017-01-31'

   3,C,'2016-04-13','2019-02-02'

I need to create a dropdown stating Year.

After year selection, I should only see students active in that year

for example :

student A should be active for year selection of 2012,2013,2014,2015,2016

student B should be active for year selection of 2017

student C should be active for year selection of 2016,2017,2018,2019

Kindly advise the formula used for creating the Year dropdown.

[devarasu07 · Accepted solution]

HI,

Try like this

Temp:
load * Inline [
Student 1d, Student  Name, Start Date, End Date
  1,A,2012-04-01,2016-12-01
  2,B,2017-01-03,2017-01-31
  3,C,2016-04-13,2019-02-02];

Fact:
load *, Year(Date) as Year;
LOAD [Student 1d], [Student  Name],
Date([Start Date]+iterno()-1) as Date
resident Temp while [Start Date]+IterNo()-1 <=[End Date];

drop table Temp;

[Anonymous]

Thanks Devarasu,

It worked Exactly as I wanted it to be.... However I can see more latency in dashboard, as it takes more time to load my pivot every time I make any selections. Can you please suggest  how that could be avoided?

Regards,

Sonal Sabhnani

[devarasu07]

Hi,

Have you tried with Straight table instead of pivot table?(usually table/straight table is much faster than pivot ) stalwar1‌ can you share your thoughts on this issue. Thanks

Regards,
Deva

[sunny_talwar]

I believe the problem could be related to the number of rows that were present even before the while statement was implemented. If there were 1 million rows of data points in your fact table, implementing while could very easily multiple that data based on how many years each student are present. There is no easy way to handle this kind of situation... except for may be creating a link table with all the years, rather than using your fact table because fact table might have 30 fields, and it might not be a good idea to multiple all of them. Rather, just duplicate the key field and the new year field which might give somewhat better performance.

[sunny_talwar]

Also, IntervalMatch with a master calendar might be something else which can be tested