Adding of period

[nevilledhamsiri]

Hi,

As per the Script given, if I am to find out the total rent period in Years , how it should be written in the script. (This should be the difference between period from to period to. Also when I need to highlight a particular deed which reaches to be renewed before a Month head, how that could be alarmed & to appear in pivot table may be with a red flag etc.

Thanks

Neville

RENT:

LOAD BRANCH_CODE,

DEED_NUMBER,

IF(DEED_NUMBER>0,'REG','PER_LAND')AS REG_PERMIT,

ADDRESS,

  [PERIOD FROM],

   [PERIOD TO],

    [MONTHLY RENT],

    [ADVANCE PAID],

     [MONTHLY RECOVERED],

     [MONTHLY PAID]

FROM (ooxml, embedded labels, table is Sheet2);

[maxgro]

maybe with the age function

age ‒ QlikView

[nevilledhamsiri]

Please see where I have gone wrong. An error comes in data loading

RENT:

LOAD * INLINE [

       BRANCH_CODE, DEED_NUMBER, ADDRESS, PERIOD FROM, PERIOD TO, MONTHLY RENT, ADVANCE PAID, MONTHLY RECOVERED, MONTHLY PAID

    RA, 285, "NO49, BANDARANAYAKE MW, RATNAPURA", 5/1/2017, 4/30/2020, 150000, 2700000, 75000, 75000

    EH, , "NN,NHBGDT", 1/31/2017, 12/31/2017, 25000, 0, 0, 25000

];

AGE_TABLE:

LOAD*,

AGE('PERIOD TO',PERIOD FROM)AS AGE

RESIDENT RENT;

DROP TABLE RENT;

[maxgro]

Change the second load, in bold,

I reloaded without error, result in image

RENT:

LOAD * INLINE [

       BRANCH_CODE, DEED_NUMBER, ADDRESS, PERIOD FROM, PERIOD TO, MONTHLY RENT, ADVANCE PAID, MONTHLY RECOVERED, MONTHLY PAID

    RA, 285, "NO49, BANDARANAYAKE MW, RATNAPURA", 5/1/2017, 4/30/2020, 150000, 2700000, 75000, 75000

    EH, , "NN,NHBGDT", 1/31/2017, 12/31/2017, 25000, 0, 0, 25000

];

AGE_TABLE:

LOAD *,

age([PERIOD TO], [PERIOD FROM]) as AGE

RESIDENT RENT;

DROP TABLE RENT;

[nevilledhamsiri]

Dear Massimo,

Thanks so much for the time taken to help me towards this. One more thing to be clarified. As can be seen once age is calculated as done above, the results seems to be wrong. The age or the period of above two cases needs to be 1 & 3 Years but what is shown there is 0 & 2!. Please let me know the reason.

Also if I am to highlight the agreements which will fall for renewal shortly may be within one to two Months time. In such a scenario, how it could be done based on the dimension such as period to. Eg I need to highlight the agreement of which period to falls say before 31st Dec 2017. Help me to write an expression on calculated dimension & applying some background color etc.

Neville

[nevilledhamsiri]

Please look in to this!

Neville

[maxgro · Accepted solution]

As can be seen once age is calculated as done above, the results seems to be wrong. The age or the period of above two cases needs to be 1 & 3 Years but what is shown there is 0 & 2!. Please let me know the reason.

Age is calculated in completed years, maybe you should add 1

age ‒ QlikView

Also if I am to highlight the agreements which will fall for renewal shortly may be within one to two Months time. In such a scenario, how it could be done based on the dimension such as period to. Eg I need to highlight the agreement of which period to falls say before 31st Dec 2017. Help me to write an expression on calculated dimension & applying some background color etc.

I'm not sure to understand

I used this expression in the background color

     =if(today() >= ([PERIOD TO]-90) and today() <= [PERIOD TO], argb(100,255,0,0))

[nevilledhamsiri]

Dear Massimo,

Thanks,

It did work

Neville