topic how to show previous day's total? in QlikView

how to show previous day's total?

Anonymous — Mon, 03 Jun 2013 11:31:36 GMT

hi all,

i wish to show last day's sum (end of day) as today's opening sum of current day. it works well except on sundays where the prvious day was weekend and had no activity.

on sunday my opening sum is 0 insted of friday's closing sum

that's my script in the table's expression:

=If(Date>Date(Today()),sum({$<Day={$(=Day(Today()))},

Month={$(=Month(Today()))},

Year={$(=Year(Today()))}>}TOTAL<CompanyDesc,BankName,AccountID,CurrencyName>

CloseAmtCurr)

+ rangesum(Before(Sum({<TranType={'income'}>} TranAmtCurr),1,Columnno()))

+ rangesum(Before(Sum({<TranType={'payment'}>} TranAmtCurr),1,Columnno())),

if(Date=Date(Today()) AND DayNames='sun',sum({$<Day={$(=Day(Today()-1))},

Month={$(=Month(Today()-1))},

Year={$(=Year(Today()-1))}>}TOTAL<CompanyDesc,BankName,AccountID,CurrencyName>

CloseAmtCurr),sum(CloseAmtCurr)))

thanks for any help

avner

Re: how to show previous day's total?

Mon, 03 Jun 2013 11:54:35 GMT

Maybe firstworkdate() function will help you, cut from help:

firstworkdate(end_date, no_of_workdays {, holiday} )

Returns the latest starting date to achieve number_of_workdays (Monday-Friday) ending no later than end_date taking into account any optionally listed holidays. End_date and holiday should be valid dates or timestamps.

Examples:

firstworkdate ('2007-03-01', 9) returns '2007-02-19'

firstworkdate ('2006-12-31', 8, '2006-12-25', '2006-12-26') returns '2006-12-18'

Re: how to show previous day's total?

Anonymous — Thu, 06 Jun 2013 05:30:08 GMT

Thanks for the tip,

but apparently I had to change an "and" condition into an "or"

problem solved

All the best