topic Re: YTD in QlikView

YTD

Wed, 04 Apr 2012 02:20:41 GMT

i have month wise data , and i want to apply YTD using bar chart. and m nt able to understand what vl b expression for YTD condition?

YTD

Wed, 04 Apr 2012 05:15:55 GMT

Hi yojassamarth,

create a new variable

varMaxMonth = Max(MONTH)

Sum({$<MONTH={"<=$(varMaxMonth)"}>} Amount)

Thanks,

Anant

YTD

Wed, 04 Apr 2012 05:35:32 GMT

Hi,

The Expression which Anant has given should work but it may need a little modification.

To make it work properly if you have data for more than 1 year, you also need to pass year.

varMaxMonth = Max(MONTH)

varMaxYear = Max(YEAR)

Sum({$<YEAR = {$(varMaxYear)}, MONTH={"<=$(varMaxMonth)"}>} Amount)

Re: YTD

Wed, 04 Apr 2012 05:49:16 GMT

yah tat rights, after adding Year condition the expression will be all right,

Thanks paliwal

YTD

Wed, 04 Apr 2012 08:30:11 GMT

but in my main table m havin period field instead of year and month. and its in d format of,

Apr09

Mar11

jan12...........

and i connected this period field with my main calender's field and in ur expression i used my calender fields as year and month then m nt getting ny data........ y it so??

yojas

Re: YTD

adhudson — Wed, 04 Apr 2012 08:41:07 GMT

Hi,

Try using the modified expression as below.

Sum({$<YEAR = {$(varMaxYear)}, MONTH={"<=$(#varMaxMonth)"}>} Amount).

or somthing like this.

Sum({$<YEAR = {$(varMaxYear)}, MONTH={"<=$(#=varMaxMonth)"}>} Amount).

Regards

Hudson

YTD

SunilChauhan — Wed, 04 Apr 2012 08:59:22 GMT

Monthfield

Apr09

Mar11

jan12...........

left(Monthfield),3 as Month.

Num(20&right(Monthfield,2)) as Year

Load * inline [

Month,MonthNo

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

and use below formula

varMaxMonth = Max(MonthNo)

varMaxYear = Max(YEAR)

Sum({$<YEAR = {$(varMaxYear)}, MONTH={"<=$(varMaxMonth)"}>} Amount)

hope this helps

YTD

Wed, 04 Apr 2012 10:37:11 GMT

Thanks....