Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need the count of active licenses for selected period.
I have license data in which LicenseID, ActivationDate, ExpiryDate and some other fields are there. I linked ActivationDate field to master calendar. I am not getting correct results. Please suggest the data model changes and give the expression to achieve this.
Thanks,
Girish
Hi,
Licnese:
LOAD * Inline
[
LicenseID, ActivationDate, ExpiryDate,LicenseDesc
101,01/01/2016,12/12/2017,Microsoft
102,20/09/2017,09/09/2018,Sharepoint
103,05/06/2018,01/12/2020,Oracle
]
;
Dates:
LOAD
LicenseID,
Date(ActivationDate+IterNo()-1) as ReferenceDate
Resident Licnese
While IterNo()<=ExpiryDate-ActivationDate+1;
CalendarMaster:
LOAD
Date(ReferenceDate) AS ReferenceDate,
Year(ReferenceDate) AS Year,
'Q' & Ceil(Month(ReferenceDate) / 3) AS Quarter,
Month(ReferenceDate) As Month,
Day(ReferenceDate) As Day,
Week(ReferenceDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS ReferenceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(ReferenceDate) AS MinDate,
Max(ReferenceDate) AS MaxDate
RESIDENT Dates;
/*
Thanks,
Mohammed Mukram
Currently I am getting the number of licenses activated in the selected period. But what i need is number of active licenses in selected period.
Hi Girish,
To know the Active Licenses in the selected period you have to generate the all the dates between ActivationDate, ExpiryDate of each LicenseID.
Kindly find the attached Sample file,
I hope it will help you.
Thanks,
Mohammed Mukram.
Licnese:
LOAD * Inline
[
LicenseID, ActivationDate, ExpiryDate,LicenseDesc
101,01/01/2016,12/12/2017,Microsoft
102,03/09/2017,09/09/2018,Sharepoint
103,05/06/2018,01/12/2020,Oracle
]
;
Hi Mohammed,
I have QV personal edition.
Could you please paste the script in body so that i can refer that.
Thanks,
Girish
Hi,
Licnese:
LOAD * Inline
[
LicenseID, ActivationDate, ExpiryDate,LicenseDesc
101,01/01/2016,12/12/2017,Microsoft
102,20/09/2017,09/09/2018,Sharepoint
103,05/06/2018,01/12/2020,Oracle
]
;
Dates:
LOAD
LicenseID,
Date(ActivationDate+IterNo()-1) as ReferenceDate
Resident Licnese
While IterNo()<=ExpiryDate-ActivationDate+1;
CalendarMaster:
LOAD
Date(ReferenceDate) AS ReferenceDate,
Year(ReferenceDate) AS Year,
'Q' & Ceil(Month(ReferenceDate) / 3) AS Quarter,
Month(ReferenceDate) As Month,
Day(ReferenceDate) As Day,
Week(ReferenceDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS ReferenceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(ReferenceDate) AS MinDate,
Max(ReferenceDate) AS MaxDate
RESIDENT Dates;
/*
Thanks,
Mohammed Mukram