topic Re: calculate previous six month value in QlikView

calculate previous six month value

arulsettu — Tue, 24 Mar 2015 04:30:20 GMT

i need to calculate previous six month value so i used below code

sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'>=$(vTBL_month)'},TBAL_CAL_YEAR={'>=$(Acnt_year)'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

vTBL_month=max(TBAL_CAL_MONTH)-6

Acnt_year=max(TBAL_CAL_YEAR)

TBAL_CAL_MONTH contains value like this

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

what i need is if i select 12th month it should show 6th month value. if i select 6th month it should show previous year 12th month value.

plz anyone suggest some idea

thanks

Re: calculate previous six month value

sushil353 — Tue, 24 Mar 2015 04:50:13 GMT

Hi,

It would be better if you use date field contain month and year together and then use addmonths function

vmonth = Addmonths(Date,-6)

if you don't have date field then you can use makedate function to create the date as makedate(2001,5)

HTH

sushil

Re: calculate previous six month value

sagarkharpude — Tue, 24 Mar 2015 04:55:22 GMT

Create the variable as

vPrior6=date(AddMonths(MonthYear,-6),'YYYY-MM')

And use it set analysis as below

sum({$<ACNT_FLEX_02={'Balance Sheet'},YearMonth={$(=vPrior6)}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Create the YearMonth field in script.

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 04:55:26 GMT

i dont have date field. only TBAL_CAL_MONTH and TBAL_CAL_YEAR

can you give the solution

thanks for replying

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:00:54 GMT

its showing only zeros

thanks

Re: calculate previous six month value

sagarkharpude — Tue, 24 Mar 2015 05:02:03 GMT

share sample app.

Re: calculate previous six month value

Anonymous — Tue, 24 Mar 2015 05:04:47 GMT

You can make a date field using the makedate() function in the script.

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:05:27 GMT

here is the sample app

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:06:30 GMT

yes i created like this

MakeDate(TBAL_CAL_YEAR,TBAL_CAL_MONTH) as datefield

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:13:00 GMT

created variable like this vPrior6= AddMonths(datefield,-6)

in chart used this

sum({$<ACNT_FLEX_02={'Balance Sheet'},MonthYear={$(=vPrior6)}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

but getting zero only

plz suggest something

Re: calculate previous six month value

sushil353 — Tue, 24 Mar 2015 05:18:20 GMT

Hi,

Create a variable like vMonth = Addmonths(Max(datefield),-6)

then use this variable in your expression like

Sum({<datefield = {">= $(vMonth)"}>}Value)

HTH

Sushil

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:23:14 GMT

Hi Sushil,

i did the same but getting zero only am i doing any mistake?

thanks

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:32:31 GMT

Hi Sushil,

i am getting the result but it is not correct. its not showing previous six month value. it showing current value

plz suggest

thanks

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:42:18 GMT

any idea guys?

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 05:51:04 GMT

even i select the year its shows only current year

plz check this app and suggest some solutions

thanks

Re: calculate previous six month value

arulsettu — Tue, 24 Mar 2015 06:05:00 GMT

any help guys?