Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Having problem with filter in sum

Hi I'm having trouble with setting a filter in a SUM statement.  I'm defining a measure to always return a total amount for a specific month.  I'm trying to use the monthstart function within the measure definition but can't seem to get it right.

If I have the measure defined like this it works:

Sum ({$<[Invoice date]={"4/20/2015"}>}[Total Charge amount])

But what I really want is to get the charges for the entire month of April so I changed it to this:

Sum ({$<Monthstart([Invoice date])={"4/1/2015"}>}[Total Charge amount])

But that gives me an error in the expression.  I can get rid of the error by doing this:

Sum ({<$(Monthstart([Invoice date]))={"4/1/2015"}>}[Total Charge amount])

But that returns no data.

I verified what the values are after Monthstart is applied by modifying the dimension in a table and the values for April all show "4/1/2015"

Thanks in advance for any help.

8 Replies
MVP
MVP

Re: Having problem with filter in sum

Try this:


Sum ({$<[Invoice date]={"(=MonthStart([Invoice date]) = '4/20/2015')"}>}[Total Charge amount])

Not applicable

Re: Having problem with filter in sum

Thanks for the reply.  I may not have explained what I'm looking for well.  Your solution above looks like it is looking for anything with an Invoice Date of 4/1/2015.  The Invoice dates will vary for example 4/8/2015, 4/20/2015.  What I'm looking to do is have all those invoices come back as part of the sum value.

MVP
MVP

Re: Having problem with filter in sum

I understood your requirement, but I think I had a slight typo in the expression, do you mind trying this and seeing if this works:

Sum({$<[Invoice date]={"(=Date(MonthStart([Invoice date]), 'M/DD/YYYY') = '4/01/2015')"}>}[Total Charge amount])


This expression should Sum the amounts for all April Invoice Dates.


Best,

Sunny

Not applicable

Re: Having problem with filter in sum

Hi, yes, I saw that in your first post and changed it to '4/10/2015' but it still returns no data.

MVP
MVP

Re: Having problem with filter in sum

Is there any way for you to share your application so that I can have a look at it?

Best,

Sunny

MVP
MVP

Re: Having problem with filter in sum

And did you do '4/10/2015' or '4/01/2015'? Just want to make sure we are not doing typo mistakes here.

Additionally I added the date format as well, just so if you have a different format in your database it would fix that problem

Sum({$<[Invoice date]={"(=Date(MonthStart([Invoice date]), 'M/DD/YYYY') = '4/01/2015')"}>}[Total Charge amount])


Try copy pasting the above expression and see if you still don't get a result. If you do great, if not then I would be able to share a sample?


Best,

Sunny

Not applicable

Re: Having problem with filter in sum

Thanks for your continued help but unfortunately it still doesn't give a result. 

MVP
MVP

Re: Having problem with filter in sum

Damn it ... Would you be able to share a sample to look at it?