Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
rcandeo
Creator III
Creator III

How to select the amount accumulated in a specific month

In a dinamic table I need to set an expression that summarizes the amount till the selected month.

If I use this expression:

sum ({$<Month = {'<=$(=max(Month))'}} amount

It works if Month is a numeric field (eg: 01, 02, 03, ...).

But it Month is defined as alpha, this expression doesn´t works (eg: jan, feb, mar, ...)

How can I make this  works in an alpha field without changing it to numeric Please?

4 Replies
pover
Partner - Master
Partner - Master

There is a small difference between ' and " when using Set Analysis.  The " runs the search engine and is necessary if you are going to use <, >, <= or >= in your Set Analysis.

So, try the following

sum ({$<Month = {"<=$(=max(Month))"}} amount)

Karl

rcandeo
Creator III
Creator III
Author

Unfortunatelly change ' by " did not worked.

If I select "jan" and put this expression

sum ({$<Month = {"*"}} amount), I receive the amount from january and february,

But If I put this expression

sum ({$<Month = {"<=$(=max(Month))"}} amount), I receive a null value -> -

Any idea?


pover
Partner - Master
Partner - Master

How are you generating your Month field?  Do you use the month() function in QV?

What I'm trying to find out is if the Month field is just a string or a dual type that contains the strings and a number behind it.  If it has a number behind the string, you should be able to create a text box with the following expression and get the number of the Month as a result:

=num(Month)

If that expression gives you a null value, and you can't change the script, I would try to use another field like Date.

For example,

sum ({$<Month=, Date= {"<=$(=max(Date))"}} amount)

The Month= is necessary to ignore the Month the user is selecting so that Date can accumulate.

Karl

rcandeo
Creator III
Creator III
Author

Thank you for you help. I´ve created another field called monthnum with numeric value and used it for the selection.