topic Re: Today Vs. Today-1 (Weekdays only) Balances in QlikView

Today Vs. Today-1 (Weekdays only) Balances

Tue, 05 Aug 2014 13:11:04 GMT

Hi,

I've got the below formula showing the difference Today Vs Yesterday.

However, when choosing a monday (for instance) the formula will compare balances Monday Vs. Sunday (no data, same as friday), so basically it will compare a balance monday with a zero balance sunday.

({$<NumDate={$(=max(NumDate))}>}Balance)-sum({$<NumDate={$(=max(NumDate)-1)},Date=>}Balance)

Num date is configured as below:

Num([date dd.mm.yyyy]) As NumDate

Can I use the (TempDate) as WeekDay from my master calendar some way?

Thanks in advance,

Olle

Re: Today Vs. Today-1 (Weekdays only) Balances

Anonymous — Tue, 05 Aug 2014 13:21:46 GMT

Try with Networkdays

Re: Today Vs. Today-1 (Weekdays only) Balances

MK_QSL — Tue, 05 Aug 2014 13:24:47 GMT

Use below without using NumDate

=SUM({<InvoiceDate = {"$(=Date(Max(InvoiceDate),'DD.MM.YYYY'))"}>}Balance) - =SUM({<InvoiceDate = {"$(=IF(WeekDay(Date(Max(InvoiceDate)-1,'DD.MM.YYYY'))='Sun',Date(Max(InvoiceDate)-2,'DD.MM.YYYY'),Date(Max(InvoiceDate)-1,'DD.MM.YYYY')))"}>}Balance)

Re: Today Vs. Today-1 (Weekdays only) Balances

Tue, 05 Aug 2014 13:41:27 GMT

Hi Manish. Returning Null values when I try it out.

Kind Regards,

Olle

Re: Today Vs. Today-1 (Weekdays only) Balances

MK_QSL — Tue, 05 Aug 2014 13:52:45 GMT

Provide sample data alongwith your requirements.

Re: Re: Today Vs. Today-1 (Weekdays only) Balances

Tue, 05 Aug 2014 14:12:27 GMT

Please find attached sample file. Thanks in advance

//Olle

Re: Today Vs. Today-1 (Weekdays only) Balances

MK_QSL — Tue, 05 Aug 2014 14:20:49 GMT

Today

=SUM({<Date = {"$(=Date(Max(Date),'DD.MM.YYYY'))"}>}Balance)

Yesterday

SUM({<Date = {"$(=IF(WeekDay(Date(Max(Date),'DD.MM.YYYY'))='Mon',Date(Max(Date)-3,'DD.MM.YYYY'),

IF(WeekDay(Date(Max(Date),'DD.MM.YYYY'))='Sun',Date(Max(Date)-2,'DD.MM.YYYY'),

Date(Max(Date)-1,'DD.MM.YYYY'))))"}>}Balance)

Difference

=SUM({<Date = {"$(=Date(Max(Date),'DD.MM.YYYY'))"}>}Balance) - SUM({<Date = {"$(=IF(WeekDay(Date(Max(Date),'DD.MM.YYYY'))='Mon',Date(Max(Date)-3,'DD.MM.YYYY'),

IF(WeekDay(Date(Max(Date),'DD.MM.YYYY'))='Sun',Date(Max(Date)-2,'DD.MM.YYYY'),

Date(Max(Date)-1,'DD.MM.YYYY'))))"}>}Balance)

Re: Today Vs. Today-1 (Weekdays only) Balances

Tue, 05 Aug 2014 14:24:23 GMT

Great stuff, thanks!

Re: Today Vs. Today-1 (Weekdays only) Balances

male_carrasco — Tue, 05 Aug 2014 14:28:53 GMT

Try to:

firstworkdate (max(NumDate), 1)

({$<NumDate={$(=max(NumDate))}>}Balance)-sum({$<NumDate={$(=firstworkdate (max(NumDate), 1) )},Date=>}Balance)

Ale.-

Re: Today Vs. Today-1 (Weekdays only) Balances

Wed, 06 Aug 2014 08:41:29 GMT

Hi Manish,

Now that I try to apply this to my master file, all of the three are returning zero-values. Script has the same structure as the sample file and the master calendar is identical. Any ideas of where to start looking?

Thanks in advance,

Olle