topic Re: Projects expiring 3,6,9 months in QlikView

Projects expiring 3,6,9 months

Mon, 21 Sep 2015 10:50:34 GMT

I have two fields 'Project_Name' and 'Project_Expiry'. How do I show projects expiring within the next 3, 6, and 9 months ?

Thanks in advance

Re: Projects expiring 3,6,9 months

Anonymous — Mon, 21 Sep 2015 12:29:29 GMT

First you need to make sure if you can use days or months.

like for three months you can use 90,120 days and so on

like this :

if(Today()-Project_Expiry>90, if(Today()-Project_Expiry<120,90))

Thanks

BKC

Re: Projects expiring 3,6,9 months

Tue, 06 Oct 2015 11:01:10 GMT

Thanks but Im not sure if this is quite what I'm after. What I need is a new field to show all projects expiring 'within' the next 3, 6 and 9 months not between ....

Re: Projects expiring 3,6,9 months

Digvijay_Singh — Tue, 06 Oct 2015 11:12:01 GMT

See if this helps -

Load Project, Date(Date#(ProjectExpiry,'DD-MM-YYYY'),'DD-MM-YYYY') as ProjectExpiry inline [

Project, ProjectExpiry

A, 15-10-2015,

B, 15-11-2015,

C, 15-12-2015,

D, 15-01-2016,

E, 15-02-2016

F, 15-03-2016

G, 15-04-2016

H 15-05-2016 ]

Re: Projects expiring 3,6,9 months

Tue, 06 Oct 2015 16:24:25 GMT

Thanks this looks good. What if I wanted to show the number of projects rather than the names ?

Re: Projects expiring 3,6,9 months

Digvijay_Singh — Tue, 06 Oct 2015 17:01:55 GMT

=Count(if(ProjectExpiry<=Date(AddMonths(Today(),+6)),Project))

Re: Projects expiring 3,6,9 months

Tue, 06 Oct 2015 18:22:04 GMT

Unfortunately still having trouble with the syntax. Fyi my date is a variable called $(vDate)

Re: Projects expiring 3,6,9 months

Digvijay_Singh — Tue, 06 Oct 2015 18:26:40 GMT

You mean you want to replace today() with the variable date? can you share your date format, will provide you the syntax with your date format.

Re: Projects expiring 3,6,9 months

Tue, 06 Oct 2015 18:32:47 GMT

Sorry ignore that.

I used your statement :

=Count(if(ProjectExpiry<=Date(AddMonths(Today(),+6)),Project))

However I get an 'error in calculation' error in my chart. ps the 'project expiry' date format is m/d/yyyy.

Many thanks

Re: Projects expiring 3,6,9 months

MK_QSL — Tue, 06 Oct 2015 18:40:00 GMT

=Count(if(Date#(ProjectExpiry,'m/d/yyyy')<=Date(AddMonths(Today(),+6)),Project))

Re: Projects expiring 3,6,9 months

Digvijay_Singh — Tue, 06 Oct 2015 18:46:09 GMT

In which chart you are using this, it works on text box, need to see where you are using it to understand the problem.

Re: Projects expiring 3,6,9 months

Wed, 14 Oct 2015 14:08:41 GMT

I get an error in calculation. I believe this is because you can not use group functions directly in calculated dimension.

If I wanted to add this as an expression instead what should it look like ?