problem in set analysis

[arulsettu]

hi

     i used this set analysis

TBAL_CAL_MONTH={$(=max(TBAL_CAL_MONTH)-6)}

its giving last six month value. ex if i select 12th month its showing 6th month value. but if i select 5th month it showing blank values

it should show last year 12th month value.

why its not showing  please clarify anyone?

thanks

[PrashantSangle]

Hi,

debug your expression in text object

just write

=max(TBAL_CAL_MONTH)-6

in text object

and then select value from TBAL_CAL_MONTH field

Regards

[arulsettu]

ya i did that too. up to 6th month its showing correct. if i select it showing -1. i dont know why

please help

thanks

[peterwh]

Hello,

it's simple: If your number of the month is 5 and you substract 6, you get -1, because the month is simply a number. If you select 3 and substract 6, you get -3. To get what you want you have to use a date-field and the AddMonths(startdate, n)-function. If you set n = -6 you get a date which is 6 months earlier than the startdate.

Kind regards

Peter

[arulsettu]

any help guys?

[PrashantSangle · Accepted solution]

Hi,

Are your creating TBAL_CAL_MONTH from any Date Field??

When you subtracting 6 from 5 then you will get -1 not 12 because Qlikview dont understand that you are subtracting month it is consider as subtracting a number from another number.

Hope you get reson for getting -1.

Try

if you are creating month number  field from any Date Field then use

AddMonths()

like AddMonths(max(DateField),-6)

then you will get expected result.

Regards

[arulsettu]

HI Peter,

               Like this

=date(AddMonths(TBAL_CAL_MONTH,-6),'MMM')

result for this showing jul only not changing can you tell me how to do it?

thanks

[stevedark]

You need to deal with when the month goes into the prior year, eg:

TBAL_CAL_MONTH={$(=max(TBAL_CAL_MONTH)+if(max(TBAL_CAL_MONTH) <= 7, 6, -6))}

And also take year into account:

TBAL_CAL_YEAR={$(=max(TBAL_CAL_YEAR)+if(max(TBAL_CAL_MONTH) <= 7, -1, 0))}

The code may not be exact, as I can't test it, but hopefully you get the idea

Steve

[arulsettu]

hi Max,

          i dont have any date field i have only month and year

[peterwh]

No,

it's wrong again. You can't only work with a Month-Field. A month is only a value between 1 and 12, but you need a complete date: 2015-03-25.

Like this:

=date(AddMonths(TBAL_CAL_DATE,-6),'MMM')

with TBAL_CAL_DATE as datefield.

I've seen your other answer,

so you can do this

=date(AddMonths(makedate(TBAL_CAL_Year, TBAL_CAL_MONTH),-6),'MMM')

With makedate(TBAL_CAL_Year, TBAL_CAL_MONTH) you create a date like 2015-03-01, if you only have Year and Month)

Kind regards

Peter