Dates Function

[#1]

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?

[bill_mtc]

Do you have sample data for this?

[jagan]

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.

[Alejandro_Hernández]

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)

[#5]

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)

[#6]

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??

[jagan]

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.