3 Replies Latest reply: Apr 2, 2017 8:18 PM by Sunny Talwar

# Accumulate days in a table

Hey guys! I need your help to translate a calculated field in excel to qlikview script.

I have this table in excel

IDreasondate_fromdate_to
1A101/01/201730/01/2017
1A101/01/201714/01/2017
1B115/01/201715/02/2017
1C113/02/201728/02/2017
2A105/02/201707/02/2017
2B106/02/201715/02/2017

I need to generate new fields that I could perform in Excel but in the script

IDreasondate_fromdate_toQ_DAYSQ_DAYS_REASONTOTAL_Q_DAYS
1A101/01/201730/01/20173030

59

1A101/01/201714/01/201714059
1B115/01/201715/02/2017311659
1C113/02/201728/02/201716

13

59
2A105/02/201707/02/20173311
2B106/02/201715/02/201716811

For the field Q_DAYS I made the following formula in Excel: DATE_TO - DATE_FROM + 1

For the field TOTAL_Q_DAYS I made =+MAX(date_to)-MIN(DATE_from)+1

For the Field Q_DAYS_REASON i made the following: the Q_DAYS for the min value of (date_from) is the same and in the second field I made =+IF(QDAYS>previous(Q_DAYS),Date_to-previous(Date_to),0)

What i need to do in this field is calculate If this date_To is greater than the previous date_to, add up the difference between the dates_to

for example:

 ID reason date_from date_to Q_DAYS Q_DAYS_REASON 1 A1 01/01/2017 30/01/2017 30.00 30 1 A1 01/01/2017 13/02/2017 14.00 44 1 F1 15/01/2017 15/02/2017 2.00 31 1 G3 13/02/2017 28/02/2017 13.00 16

thanks!!!

stalwar1

• ###### Re: Accumulate days in a table

what is the expected output? I am confused

• ###### Re: Accumulate days in a table

kushal i need to generate a table like the second one, with the fields Q_DAYS, Q_DAYS_REASON and TOTAL_Q_DAYS

I made an excel like the attachment but i need to make the fields in a large table in qlikview script.

I hope I have explained well

• ###### Re: Accumulate days in a table

Try this

Table:

Date(If(ID = Previous(ID), RangeMax(Peek('date_to'), Peek('max_date_to')), date_to)) as max_date_to,

If(ID = Previous(ID), If(date_to > Previous(date_to), RangeSum(date_to, -RangeMax(Peek('date_to'), Peek('max_date_to'))), 0), date_to - date_from + 1) as Q_DAYS_REASON;

ID,

reason,

date_from,

date_to,

date_to - date_from + 1 as Q_DAYS

FROM

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)