topic Re: getting wrong value in set analysis in QlikView

getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 04:59:37 GMT

HI,

to get last six month value i got this code from experts

Sum({$<ACNT_FLEX_02={'Balance Sheet'},datefield={">=$(=date(Addmonths(max(datefield),-6)))<=$(=date(max(datefield)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

when implement for example if i select 2014 12th month it show 2014 6th month. up to this correct. but the value for the last six month is same as the current value. there is no difference.

please some one clarify its really emergency...

thanks

Re: getting wrong value in set analysis

sushil353 — Wed, 25 Mar 2015 05:09:09 GMT

Hi,

Try to separate your expression as

Sum({$<ACNT_FLEX_02={'Balance Sheet'},datefield={">=$(=date(Addmonths(max(datefield),-6)))<=$(=date(max(datefield)))"}>}TBAL_MTD_DR_LC_1) - Sum({$<ACNT_FLEX_02={'Balance Sheet'},datefield={">=$(=date(Addmonths(max(datefield),-6)))<=$(=date(max(datefield)))"}>}TBAL_MTD_CR_LC_1)

HTH

Sushil

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 05:12:10 GMT

hi,

Bypass month,year

Sum({$<Month=,Year=, ACNT_FLEX_02={'Balance Sheet'},datefield={">=$(=date(Addmonths(max(datefield),-6)))<=$(=date(max(datefield)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Regards

Vimlesh

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 05:13:37 GMT

Hi,

Check this app.

Regards

Vimlesh

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 05:41:02 GMT

sorry for the confusion

my requirement is i have month field like this

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

if select 12th month it should show the value of 0 to 6th month value

this i am trying please help

above code showing 6th to 12th

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 05:44:47 GMT

hi,

Try this

=Sum({$<Month=,Fin_Year=, Date={">=$(=date(Addmonths(max(Date),-12)))<=$(=date(Addmonths(max(Date),-6)))"}>}Value)

Regards

Vimlesh

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 05:48:32 GMT

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 05:50:30 GMT

Hi Vimlesh,

it showing value between 12/1/2013 to 6/1/2014

not calculating the 0

my month starts with 0.

thanks

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 05:54:01 GMT

dear ,

month start 1 to 12.

how are you showing 0 to 12 .... its 13 months in a year.

for this you make variable of months only like

vminmonth=month(date)-6

vmaxmonth=(month(date)-12

and bypass year,date etc.

Regards

Vimlesh

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 05:56:11 GMT

please check this app

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 05:59:11 GMT

output is if i choose 2014 and 12th month TBAL_COMP_CODE=001

Cash and cash equivalents

784202429

this is the expecting result

plz anyone check it

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 06:00:36 GMT

hi ,

check this app.

may be helpful.

Regards

Vimlesh

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 06:06:38 GMT

hi in this app u your having date field but in my case i am having only year and month field

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 06:11:17 GMT

any suggestions plz its emergency.....

Re: getting wrong value in set analysis

Wed, 25 Mar 2015 06:15:21 GMT

hi,

I have sent you last app .in which i have used nummonth and year two fields (ignore date fields).

=Sum({$<Date=,Fin_Year=, nummonth={">=$(=vminmonth )<=$(=vmaxmonth)"}>}Value)

variables= >>> vminmonth=nummonth-12

vmaxmonth=nummonth-6

Regards

Vimlesh

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 06:17:22 GMT

this i done

=Sum({$<TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-12)))<=$(=date(Addmonths(max(datefield),-6)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

it shows value between 1/1/2014 to 6/1/2014

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 06:20:11 GMT

ya but i choose month 2 means max(month) goes in negative so i get blank values

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 06:27:16 GMT

in my sample app values calculating 1 to 6th month i need to calculate 0th value in month also so i did this

=sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+Sum({$<TBAL_CAL_MONTH=,TBAL_CAL_YEAR=,ACNT_FLEX_02={'Balance Sheet'}, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-6)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

if any mistake please correct me. using this code i didnt get correct answer

Re: getting wrong value in set analysis

arulsettu — Wed, 25 Mar 2015 06:39:47 GMT

HI shusil,

can you suggest some idea please

thanks