topic Re: script aggregation/accumilation in QlikView

script aggregation/accumilation

Thu, 03 Mar 2016 13:11:42 GMT

I have a calendar with all dates in the year. In the calendar there is a flag field (1 or 0) to indicate if the day was an Invoice day. I am trying to create a field with the count of invoice date in the month. example Feb 2016 has no public holidays so 21 possible invoice day (Mon - Fri). I want Monday the 1st to be 1, Tuesday the 2nd to be 2 and so on. I think I need to combine the rangesum and peek functions to accumulate the flag and create my InvoiceDayOfMonthNumber but I cant figure out how I get it to start at 1 again on the change of a month.

Thanks

Oli

Re: script aggregation/accumilation

sunny_talwar — Thu, 03 Mar 2016 13:20:34 GMT

Do you have a sample dataset you can share? It would be easy to guide with a sample and expected output

Re: script aggregation/accumilation

Gysbert_Wassenaar — Thu, 03 Mar 2016 13:26:19 GMT

If you have a month field in your calendar and the calendar is in chronological order you can use something like:

If(MyMonthField = Previous(MyMonthField),

rangesum(IsInvoiceDayFlag, peek(InvoiceDayOfMonthNumber)),

IsInvoiceDay) as InvoiceDayOfMonthNumber

Re: script aggregation/accumilation

Fri, 04 Mar 2016 10:33:46 GMT

I had a change of tact which seems to be the way to go with some tweaking

Ref:

https://community.qlik.com/thread/208191