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: 
Not applicable

Subtract/deselect end/max month

Hi

I have this simple code:

=if(sum({$}Month), LightRed(), lightgray())

The only thing I need is to say minus one Month, that is the last one. So if I choose "Jan, Feb and Mar", I need it too remove Mar. If I choose "Jun, Jul", I need it to only use Jun and so forth.  

21 Replies
richard_pearce6
Luminary Alumni
Luminary Alumni

Depends on your Month field. Is it 1-12 or is it MonthStart DD/MM/YYYY for example?

If it's a Date feild (DD/MM/YYYY) you could use the addmonths() function to go one month prior.

if its 1-12 you'll stuggle as going backwards from 1 is a challange.

You could also add an incremental month number in your calendar or data table starting at 1 and increasing every month throughout your data load. For example two years of data = 1 to 24. You can get the max selected from that and minus 1 for your set analysis.

If you want an AsOf calendar there's one here: http://community.qlik.com/docs/DOC-6593

Regards

Richard

Not applicable
Author

thx, but so far there is no date Field, it might come later.

Can it be done with just the string?

Not applicable
Author

Mikael.. You can't sum a string..

However you can cast your strings as dates as follows:

num(month(date#('jan','MMM'))

You can then use an expression like if(Month < (max(Month)-1), colorA, colorB)

richard_pearce6
Luminary Alumni
Luminary Alumni

If you only have one year 1-12 then convert the Month field as per daniels suggestion below, if you have multiple years or if you cross the december boundry 8 - 12 - 1 - 7 that won't work as you still have the issue of going from 12 to 1.

Perhaps a simple fix if its just small data and you want to see results is to set up a new table

NewTable

Load * inline [

Month, Prev_Month

Jan     ,Dec

Dec     ,Nov

etc     ,etc

];

Your set expression would look like  =Sum ( {<Month={'$(=only(Prev_Month))' } >}   Value)

This would sum up the Values for Dec when the user has Jan Selected in Month. Won't work if nothings selected, there you would have to expand this idea to take the Max(Month) ensuring its a dual field.

Richard

Not applicable
Author

Richard, good suggestion!

However, I would start with 1 and continue counting.. ig. if you start your calendar in january 1st 2013, then january 1st 2014 will have number 13.

This can be accomplished easily by adding the month num and the difference in years * 12.

richard_pearce6
Luminary Alumni
Luminary Alumni

Agree Daniel, in my first comment I suggested this approach which is the simplest by far!

Main challange here is no date field and a text month field. Long term the data needs a proper date flag including into it. Hopefully Mikal has a Year feild to work with.

Richard

Not applicable
Author

You're right, you did suggest that!

Not applicable
Author

"Mikael.. You can't sum a string.."

The string is just a dim., and the code works for bar chart, but with line chart it connect to the next month.

I am afraid that you all make it too complicated, I just need to go one back, as it is.      

richard_pearce6
Luminary Alumni
Luminary Alumni

Unfortunately sometimes it is complicated.... Getting the data right means less hassle in the UI.

If you post some sample data I'm sure we can work out a solution. It's more complicated when we can't see what you have.

Richard