topic Subtract/deselect end/max month in QlikView

Subtract/deselect end/max month

Wed, 09 Jul 2014 11:26:47 GMT

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.

Re: Subtract/deselect end/max month

richard_pearce6 — Wed, 09 Jul 2014 11:32:59 GMT

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

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 11:39:33 GMT

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

Can it be done with just the string?

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 11:57:28 GMT

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)

Re: Subtract/deselect end/max month

richard_pearce6 — Wed, 09 Jul 2014 12:11:23 GMT

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

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 12:23:09 GMT

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.

Re: Subtract/deselect end/max month

richard_pearce6 — Wed, 09 Jul 2014 12:30:43 GMT

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

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 12:34:23 GMT

You're right, you did suggest that!

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 18:03:40 GMT

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

Re: Subtract/deselect end/max month

richard_pearce6 — Wed, 09 Jul 2014 18:12:50 GMT

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

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 18:22:26 GMT

In this case April to July are selected:

Unfortunately it looks like August is selected too. So far "=if(sum({$}Month), LightRed(), lightgray())" is used, and it almost work. Do I really need an extra dataset to make it perfect?

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 18:23:21 GMT

Ok, thx, see last post

Re: Subtract/deselect end/max month

Wed, 09 Jul 2014 18:25:57 GMT

I am here btw:

Re: Re: Subtract/deselect end/max month

richard_pearce6 — Thu, 10 Jul 2014 08:19:04 GMT

Morning Mikael,

Apprecaite the images, after looking at those and making a few assumptions here is a simple example of what you originally requested.

Richard

Re: Re: Subtract/deselect end/max month

Thu, 10 Jul 2014 08:59:38 GMT

ah, thx for the effort

Unfortunately I only have the PE version, until I become skilled enough to get a job working with QV.

Could you plz post the code inside this blog?

Re: Subtract/deselect end/max month

richard_pearce6 — Thu, 10 Jul 2014 09:40:29 GMT

script:

// Lets assume this is your data

Original_Data:
Load * Inline [
Month ,Value
Jan ,10
Feb ,20
Mar ,30
Apr ,40
May ,60
Jun ,70
Jul ,80
Aug ,90
Sep ,100
Oct ,110
Nov ,120
Dec ,130
];

// The month is a sting and there are no other Date fields.
// First we need to convert the text into a number

Data:
NoConcatenate Load
Value
,Month as Month_Original
,Month(Date#(Month,'MMM')) as Month_Integer // This is a dual feild so it will read as text but treated as a number
Resident Original_Data;

Drop Table Original_Data;

Re: Subtract/deselect end/max month

richard_pearce6 — Thu, 10 Jul 2014 09:41:37 GMT

Use Month_Interger as the chart dimension and the expression is

Sum

( {<Month_Integer={">$(=min(Month_Integer))<=$(=max(Month_Integer))"}>} Value)

Re: Subtract/deselect end/max month

richard_pearce6 — Thu, 10 Jul 2014 09:43:44 GMT

A further expression can also be added:

Sum

( {<Month_Integer=>}Value)

Will give you the full year regardless of the selection made in Month_Integer. By promoting it higher than the first epression it will show behind and will give you the same effect as your image.

Richard

Re: Subtract/deselect end/max month

richard_pearce6 — Thu, 10 Jul 2014 12:13:09 GMT

You don't have to drop your table. Just add the line transforming the text month into an integer.

I only dropped the table because this is an example.

Richard

Re: Subtract/deselect end/max month

Wed, 03 Sep 2014 10:49:57 GMT

Sorry for a late response, but I just want to confirm that it works thx.