Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Dates Function

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?

6 Replies
bill_mtc
Contributor

Re: Dates Function

Do you have sample data for this?

MVP
MVP

Re: Dates Function

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.

Employee
Employee

Re: Dates Function

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)

Not applicable

Re: Dates Function

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)

Not applicable

Re: Dates Function

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

MVP
MVP

Re: Dates Function

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.

Community Browser