Visokio website     Downloads     Video tutorials     KnowledgeBase  
Demo: Dynamic calculation of investments with different maturity dates - Visokio Forums
Demo: Dynamic calculation of investments with different maturity dates
  •     paola November 17, 2015 7:16AM
    Simple demo attached, illustrating the use of DATEDIFF formula in the scenario where investments/deposits have different maturity date.
    First step is calculation of number of days between today and the investment maturity date:
    DATEDIFF(NOW, [Date], "days")  

    By using NOW function, this calculation will keep the report up-to-date, always returning the latest breakdown.
    It is also possible to 'fix' the date and use DATEVALUE("dd/MM/yyyy") instead of NOW, e.g. DATEDIFF(DATEVALUE("01/10/2015"), [Date], "days") .
    Second step is to create irregular time intervals, that will 'bucket' the records and be used for sum calculation.

    IF(
    [Difference days]<1,"matured",
    [Difference days]<2,"1 day",
    [Difference days]<8,"1 week",
    [Difference days]=<31,"1 month",
    [Difference days]<92,"3 months",
    [Difference days]<184,"6 months",
    [Difference days]<366,"1 year",
    [Difference days]<1096,"3 years",
    [Difference days]<1827,"5 years",
    [Difference days]<2557,"7 years",
    [Difference days]=<3653,"10 years",
    [Difference days]>3653,"over 10 years",
    null)  

    Aggregation and summing of investment amounts for each period is done in the Bar/Line view.
    Attachments
    Datediff.iok 91K

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership