Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have the create_date for a CI . It expires after 36 months from create_date. How do I calculate the count of CIs that expires in the next 30 days?
Do you have sample data for this?
Hi Anitha,
Arrive a new dimension Expiry date in the script like this
LOAD
*,
AddMonths(DateDimension, 36) AS ExpiryDate
FROM DataSource;
Now use this expression to get the count of CIs that expires in the next 30 days
Count({<ExpiryDate={'>=$(=Today())<=$(Date(Today() + 30))'}>} CI)
Hope this helps you.
Regards,
Jagan.
In your script add:
if(addmonths(create_date,36)-today()<=30,1,0) as ExpirationFlag
and use the following expression to get the count of CI's
=Sum(ExpirationFlag)
Please ExpiryFlag in the script level and sum that ExpiryFlag on the dashboard.
LOAD * ,
If(Expiry_Dt >= Today() AND Expiry_Dt <= Today()+30 , 1 ,0) AS ExpiryFlag
;
LOAD
*,
AddMonths(DateField, 36) AS Expiry_Dt
FROM Source;
On the UI: SUM(ExpiryFlag)
Thanks all of you for your valuable inputs.
can I use the expression while creating the dashboard and not include them in the load script?.. Does it have any performance implications??
Hi Anitha,
Definitely there is an impact on performance if you calculate dynamically in expression, the best method is if possible calculate everything in script.
Regards,
Jagan.