Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

13 Replies
oknotsen
Master III
Master III

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!
swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

‌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. 

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

‌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?

Anonymous
Not applicable
Author

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

perumal_41
Partner - Specialist II
Partner - Specialist II

Hi Peggy,

Try below exp

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

imrencimen
Contributor III
Contributor III

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])

swuehl
MVP
MVP

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