topic Re: month start giving wrong value in QlikView

month start giving wrong value

arulsettu — Mon, 23 Mar 2015 09:21:37 GMT

hi i am trying to get month from from month field

TBAL_Month ={">=$(=MonthStart(max(TBAL_Month)))<=$(=max(TBAL_Month))

result is

MonthStart(max(TBAL_Month)))=1/1/1900

but tabl_month contain jan feb mar .... dec

am i doing anything wrong here

plz give me some solutions

thanks

Re: month start giving wrong value

tresB — Mon, 23 Mar 2015 09:29:20 GMT

Possibly, your TBAL_Month field is not a proper date field. Date creation at the script might be an issue.

Re: month start giving wrong value

arulsettu — Mon, 23 Mar 2015 09:38:30 GMT

Hi Tresesco,

TBAL_CAL_MONTH it contain 0,1,2,3,4,5,6,7,8,9,10,11,12

to avoid 0 below code used

Month(date#(TBAL_CAL_MONTH,'MM')) as TBAL_Month

now i am trying to create opening balance. if i select month June i need to calculate previous months like may,apr,mar,feb,jan and that 0 as opening balance for june.

i tried this but not getting the correct value

sum({<TBAL_Month ={">=$(=MonthStart(max(TBAL_Month)))<=$(=max(TBAL_Month))"},TBAL_CAL_YEAR={'>=$(Acnt_year)'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1)-sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_CR_LC_1)

please help me

Re: month start giving wrong value

prieper — Mon, 23 Mar 2015 09:41:32 GMT

Is the above meant to be a variable? Then it can contain only one value for the month.

If meant to be an expression, you are missing aggregation (and curly brackets etc)

Peter

Re: month start giving wrong value

Mon, 23 Mar 2015 09:42:05 GMT

Hi,

What u wrote in expression that is correct only Monthstart it will give first day of the that particular month

That is why your getting 1/1/1900 this output

can u explain what u are looking for

Thanks

Manju

Re: month start giving wrong value

arulsettu — Mon, 23 Mar 2015 09:44:39 GMT

max(TBAL_Month) is not a variable

thanks

i will upload the sample file

Re: month start giving wrong value

tresB — Mon, 23 Mar 2015 09:47:05 GMT

Well, so you are not bothered about the date. All you need is month. In that case, to get month, try like:

Month(MonthStart(max(TBAL_Month))))

Re: month start giving wrong value

arulsettu — Mon, 23 Mar 2015 09:54:20 GMT

thanks Tresesco getting jan month. but not able to get the exact result can you please check below app

thanks

Re: month start giving wrong value

arulsettu — Mon, 23 Mar 2015 09:58:43 GMT

i am trying to create opening balance. if i select month June i need to calculate previous months like may,apr,mar,feb,jan and that 0 as opening balance for june.

Re: month start giving wrong value

arulsettu — Mon, 23 Mar 2015 10:08:48 GMT

any help guys?

Re: month start giving wrong value

prieper — Mon, 23 Mar 2015 13:33:33 GMT

please upload an example to see, what kind of data you use

Peter

Re: month start giving wrong value

arulsettu — Mon, 23 Mar 2015 14:43:07 GMT

please check above in this thread i uploaded a sample

Re: month start giving wrong value

prieper — Mon, 23 Mar 2015 18:10:08 GMT

TBAL_Month is not a number, but only a text (abbreviation for the month).

Think it will make sense to work with rolling monthes:

YEAR(Date)*12 + NUM(MONTH(Date))

This might be accessed with MIN/MAX or you may calculate to deduct e.g. 6 monthes etc

HTH Peter