Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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))
maybe with the age function
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;
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;
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
Please look in to this!
Neville
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
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))
Dear Massimo,
Thanks,
It did work
Neville