Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Today Vs. Today-1 (Weekdays only) Balances

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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)

View solution in original post

9 Replies
Anonymous
Not applicable
Author

Try with Networkdays

MK_QSL
MVP
MVP

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)

Not applicable
Author

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

Kind Regards,

Olle

MK_QSL
MVP
MVP

Provide sample data alongwith your requirements.

Not applicable
Author

Please find attached sample file. Thanks in advance

//Olle

MK_QSL
MVP
MVP

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)

Not applicable
Author

Great stuff, thanks!

male_carrasco
Creator
Creator

Try to:

firstworkdate (max(NumDate), 1)

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


Ale.-

Not applicable
Author

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