topic Re: Expression Explanation in QlikView

Expression Explanation

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am pretty new to qlikview and somebody kindly helped me create an expressions that returns a sum of bill value on the current month and for the full previous month on day one.

The code is sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} >} [Bill Value]

I don’t really understand ">=$(=MonthStart(Today(),Day(Today())=1))"}

I know the expression needs to be wrapped in {} and I guess the same applies for the “ after { and “ before }

>= obviously means equal to or greater than and the $ prefixes all dates calculation.

Can you explain the rest of the code is pseudo terms and how it decides to return the previous months data on day one - I know it does this because Day(Today())=1) but how does it affects =MonthStart(Today(),Day(Today())=1))"}.

Your help would be much appreciated. Is there any free online training so I can learn how to write expressions properly

Regards

Dave

Re: Expression Explanation

sunny_talwar — Wed, 13 Jul 2016 11:05:25 GMT

From what I understand, this -> Day(Today())=1 will give you -1 when true and 0 otherwise. So when we are at the first day of the month, expression will take you back to the 1st of last month. When you are any other date than 1st, it will take you to the 1st of this month.

-1 means condition is true

0 means condition is false

So look at this as an invisible if statement

If(Day(Today()) = 1, -1, 0) is equivalent of Day(Today()) = 1

To further test this, try Day(Today()) = 1 in a text box object

Re: Expression Explanation

manidhana90 — Wed, 13 Jul 2016 11:29:10 GMT

From My Understanding, Day(Today()) returns total days completed in this month,

Lets take an example, Today's Date is 7/13/2016 Then Returns 12.

But In above scenario we need to calculate till date total, so we interpreting Day(Today())=1,Then it Returns 13.

"=MonthStart(Today(),Day(Today())=1) " -> This expression is to calculate total from start of today's month to Today.

I hope this helps you to get clear idea.@Dave Adlard

Re: Expression Explanation

manidhana90 — Wed, 13 Jul 2016 11:31:28 GMT

From My Understanding, Day(Today()) returns total days completed in this month,

Lets take an example, Today's Date is 7/13/2016 Then Returns 12.

But In above scenario we need to calculate till date total, so we interpreting Day(Today())=1,Then it Returns 13.

"=MonthStart(Today(),Day(Today())=1) " -> This expression is to calculate total from start of today's month to Today.

I hope this helps you to get clear idea.@Dave Adlard

Re: Expression Explanation

Wed, 13 Jul 2016 12:42:14 GMT

Hi Sunny

got another one for you if that's OK.

This is for last full month

Sum({<[Bill Date]={">= $(=monthstart(Monthend(today()+1,-1))) <=$(=monthend(today()+1,-1))"} >} [Bill Value])

Again I am unsure of the +1, -1

Is there not some sort of joiner between the month start and month end expression

Re: Expression Explanation

sunny_talwar — Wed, 13 Jul 2016 14:17:38 GMT

From what I understand, this is what this one is doing...

Let's assume that Today is 05/31/2016

Today() + 1 = 06/01/2016

MonthStart(Today() + 1, -1) = 05/01/2016

MonthEnd(Today() + 1, -1) = 05/31/2016

So for 05/31/2016, the expression will return all data between 05/01/2016 and 05/31/2016 (this month's data)

Now let's assume that Today is infact today (07/13/2016)

Today() + 1 = 07/14/2016

MonthStart(Today() + 1, -1) = 06/01/2016

MonthEnd(Today() + 1, -1) = 06/30/2016

So for 07/13/2016, the expression will return all data between 06/01/2016 and 06/30/2016 (last month's data)

So basically if you are at the last day of the month, you will get the current month's data. If you are any date except the last date, you will get the data for last completed month.

I hope this will help clarify?

Re: Expression Explanation

Wed, 13 Jul 2016 14:27:34 GMT

Perfect thanks so it adds a day but takes away a month so it does the Today()+1 functions first and adds a day because you are using Today and then does the monthstart -1. As the -1 is applied to monthstart it removes a month.

Is my understanding correct?

Re: Expression Explanation

sunny_talwar — Wed, 13 Jul 2016 14:44:14 GMT

Yes, you are absolutely right