topic Re: Dates Function in QlikView

Dates Function

Wed, 27 Nov 2013 23:06:19 GMT

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?

Re: Dates Function

bill_mtc — Thu, 28 Nov 2013 02:17:10 GMT

Do you have sample data for this?

Re: Dates Function

jagan — Thu, 28 Nov 2013 02:44:59 GMT

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.

Re: Dates Function

Alejandro_Hernández — Thu, 28 Nov 2013 02:59:44 GMT

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)

Re: Dates Function

Thu, 28 Nov 2013 02:59:54 GMT

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)

Re: Dates Function

Thu, 28 Nov 2013 05:51:26 GMT

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

Re: Dates Function

jagan — Thu, 28 Nov 2013 06:27:23 GMT

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.