Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
thx, but so far there is no date Field, it might come later.
Can it be done with just the string?
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)
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
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.
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
You're right, you did suggest that!
"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.
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