All unallocated cash receipt till yesterday

[ugurarslan]

Dear all,

In order to see all unallocated cash (to a customer account) I use the following expression:

Sum({<

[code of cash applied]={'unallocated'},

[date of cash applied]={"<=$(=today())"}

>}[cash applied total USD])

Now I want to see what case was unapplied till yesterday. This in order to see the difference between today and yesterday.

What expression do I need to use for the date of cash applied?

Ugur

[DavidŠtorek]

Hi,

just ad -1 to your expression and it should work

Sum({<

[code of cash applied]={'unallocated'},

[date of cash applied]={"<=$(=today()-1)"}

>}[cash applied total USD])

[ugurarslan]

Thanks a lot David. This helped me.

What in case I want to see the end of last month?

Thanks

Ugur

[DavidŠtorek]

Glad to help.

In that case try to use expression like this

Sum({<

[code of cash applied]={'unallocated'},

[date of cash applied]={"<=$(=monthsend(1,addmonths(today(),-1)))"}

>}[cash applied total USD])

Functions I used :https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/add...

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/mon...

Hope that helps

[balabhaskarqlik]

Better to derive month, year fields for Date dimension in the script, then try like this:

=Sum({<[code of cash applied]={'unallocated'}, Month={$(=month(addmonths([date of cash applied],-1)))}>}[cash applied total USD])