Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

parks399
New Contributor II

Expression help -- sum based on 2 criterias

I want to make a dashboard page that reference the current month always.  I created a table with the header labeled 'Cur Mth' and one row member 'Feb'.  I have the data table with titles Month, Year, Account, Actual Am2.

sum({$<Year={2016}>,Month={$(Cur Mth)}},[Actual Amt2])

It says the expression is correct, but I am not getting any data.

If  i use just the year, it works.  sum({$<Year={2016}>}[Actual Amt2])

I can't get adding the 2nd criteria to work.  Any suggestions.

What was simple in excel is driving me nuts here.

Tags (1)
13 Replies
oknotsen
Honored Contributor III

Re: Expression help -- sum based on 2 criterias

Months are usually stored as a number (so 2 for February) and showing as text (or number, depending on what you want).

I suggest you try again, but now with a number instead.

Alternatively, if you want to use the current month, you can try to match it with the result of month(today()).

May you live in interesting times!
MVP
MVP

Re: Expression help -- sum based on 2 criterias

Peggy,

you first need to check the values of Month field. Do they show a text representation like 'Feb'?

Then you should be able to use a dollar sign expansion like Onno suggested:

=sum({$<Year={2016}>,Month={'$(=Month(Today()))'} >} [Actual Amt2])


Though this would return Mar, not Feb.


If you've created a table in your model with a field named 'Cur Mth', and a single record, you can also use


=sum({$<Year={2016}>,Month={'$(=Only([Cur Mth]))'} >} [Actual Amt2])


Hope this helps,

Stefan

parks399
New Contributor II

Re: Expression help -- sum based on 2 criterias

‌i wanted to be able to control the date range because our company period ends on a date other than the end of a calendar month.  Plus when I publish the report the month following period close and I thought the 'Today' function would change to the month you are in instead of the month just closed.   My data has Month as 'Jan',Feb', ''Mar', etc...  But I also have a Period column added referencing the date as a number. 

parks399
New Contributor II

Re: Expression help -- sum based on 2 criterias

T‌hanks.  I'll give the 'Only' function a try

parks399
New Contributor II

Re: Expression help -- sum based on 2 criterias

‌I haven't logged in yet to try this, but what is the rule for using the accent mark in front of the $ sign.  Same with the ().  can you refer to a good source that helps explain how to write expressions with multiple criteria?

parks399
New Contributor II

Re: Expression help -- sum based on 2 criterias

It didn't work.  Any other suggestions???

perumal_41
Valued Contributor II

Re: Expression help -- sum based on 2 criterias

Hi Peggy,

Try below exp

sum({$<Year={2016},Month={'$(Cur Mth)'}>} [Actual Amt2])

imrencimen
New Contributor III

Re: Expression help -- sum based on 2 criterias

Hi Peggy

Settings>VariableOverview>Add>

Variable Name :CurrYear

Select CurrYear and write Definition section  =Year(today())

Add>

Variable Name :CurrMonth

Select CurrMonth and write Definition section  =Month(today())

Controlling :Add two new SheetObjects>TextObject>Check variables are correct.

Then,

sum({<Year={'$(CurrYear)'},Month={'$(CurrMonth)'}>}[Actual Amt2])


İf you want to don't effect any filters use "1"


sum({1<Year={'$(CurrYear)'},Month={'$(CurrMonth)'}>}[Actual Amt2])

MVP
MVP

Re: Expression help -- sum based on 2 criterias

Peggy,

here is a short introduction how to use set modifiers:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-m...

In general, I use single quotes around field values in the set modifier value list, this also handles values with e.g. spaces correctly (so, you may be able to use a set modifier without single quotes like {2015,2016} , but you may experience issues in some cases).

See also

Why is it called Set Analysis?

A Primer on Set Analysis

Dates in Set Analysis

The Magic of Dollar Expansions

The Little Equals Sign

Hope this helps,

Stefan

Community Browser