Distribuite amount in number of month of contract

[fkeuroglian]

Hi Expert!

I have a problem and i want to distribuate the amount or value of the contract in the months of duration of every contract(DateEnd - DateBegin)

As the image attached show, i have the amount of month and the value for each month but i want see distribuite month by month in a bar chart for example.

I have only the date begin and the date end, i want to see for example if i have a contract for 1 one, the value of each month distribuite in every month, not only in the month that start the contract

i attached a simple qvw and qvd to this example and a image to show!

Thank you a lot

Fernando K.

[Gysbert_Wassenaar]

Perhaps this blog post helps: Creating Reference Dates for Intervals‌. You could generate records for each month in the interval between begin and end date.

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

SAEC_Contratos:
LOAD *,
AccruedAmount/ContratoMesesDeContrato AS ContratoXMes;
LOAD IDContractPack,
    AccruedAmount,
    SquareMeters,
    if(IDCurrency=2,'Dolares','Pesos UYU') AS MonedaContrato,
    date(DateBegin) as ContratoInicio,
    Date(DateEnd) as ContratoFin,
    ((year(date(DateEnd))*12)+month(date(DateEnd))) - (((year(Date(DateBegin))*12)+month(Date(DateBegin)))) as ContratoMesesDeContrato
    // ClassType
FROM [https://community.qlik.com/servlet/JiveServlet/download/920320-198705/SAEC_Contratos2.QVD](qvd);

tabCalendar:
LOAD *,
    Day(Date) as Day,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    WeekDay(Date) as WeekDay,
    Month(Date) as Month,
    MonthName(Date) as MonthName,
    'Q'&Ceil(Month(Date)/3) as Quarter,
    QuarterName(Date) as QuarterName,
    Year(Date) as Year,
    WeekYear(Date) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=MaxDate;
LOAD Min(ContratoInicio) as MinDate,
    Max(ContratoFin) as MaxDate
Resident SAEC_Contratos;

tabLink:
IntervalMatch(Date)
LOAD Distinct
    ContratoInicio,
    ContratoFin
Resident SAEC_Contratos;

hope this helps

regards

Marco