topic Re: Help with Expression with Dates in QlikView

Help with Expression with Dates

rustyfishbones — Fri, 14 Jul 2017 14:51:06 GMT

Hi All,

I have the following expression

NUM(Sum ({<[TrxType]={'R'},ActionID ={'RECEIPTS'},Year = {$(=MAX(Year))}, Month = {'$(vPriorMonth)'},Day = {"<=$(=DAY(MAX(Date)))"}>}[#Act-Prime]),'#,##0')

when I view it using a straight table it return the correct details

But it's returning no Value

It should return 2945 which is the 01/05/2017

Re: Help with Expression with Dates

sunny_talwar — Fri, 14 Jul 2017 14:54:08 GMT

What is the exact expression used in the list box object? Also, how do you define Month field in the script and how do you define vPriorMonth?

Re: Help with Expression with Dates

rustyfishbones — Fri, 14 Jul 2017 14:58:15 GMT

SUM([#Act-Prime]) is used in List Box

Month:

MONTH(Date) as Date

vPriorMonth:

=MONTH(ADDMONTHS(MAX(Date), -1))

Re: Help with Expression with Dates

rustyfishbones — Fri, 14 Jul 2017 15:01:34 GMT

I see the problem now.

No [#Act-Prime] for TrxType = {'R'} for that Date

Re: Help with Expression with Dates

rustyfishbones — Fri, 14 Jul 2017 15:01:53 GMT

So the expression is correct

Re: Help with Expression with Dates

sunny_talwar — Fri, 14 Jul 2017 15:01:55 GMT

Try this as it seems that you only have start of month in your date column

Sum({<Date = {"$(=Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY'))"}>}[#Act-Prime])

Not sure I understand the purpose of this as these are not in list box object and I wouldn't want to add them in the other expression to make sure that the number match up

[TrxType]={'R'},ActionID ={'RECEIPTS'}

Re: Help with Expression with Dates

rustyfishbones — Fri, 14 Jul 2017 15:08:49 GMT

Hi Sunny,

I am trying to compare this month to date v Last Month to Date

If if today is 14/07/2017, for this month I want everything from 01/07-14/07

and Last Month should be 01/06-14/06

It's the Last Month that I have issues with

Re: Help with Expression with Dates

sunny_talwar — Fri, 14 Jul 2017 15:11:12 GMT

For that, I would try this may be

Last Month

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Max(Date), -1), 'DD/MM/YYYY'))"}, Month, Year>}[#Act-Prime])

This Month

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Max(Date), 0), 'DD/MM/YYYY'))"}, Month, Year>}[#Act-Prime])

Re: Help with Expression with Dates

rustyfishbones — Fri, 14 Jul 2017 15:15:17 GMT

That's it thanks alot

Re: Help with Expression with Dates

sunny_talwar — Fri, 14 Jul 2017 15:15:56 GMT

No problem at all