topic Re: YTD issue in QlikView

YTD issue

Anonymous — Mon, 23 Feb 2015 17:42:40 GMT

Hi all,

I working on a requirement which need to show YTD and when i select a month it just default the value to only that month instead of showing YTD.

can some body help .. i'm attaching my Data file and QlikView file which explains the requirement with example and calucaulation required at chart level

have a great day

Sri1

Re: YTD issue

MK_QSL — Mon, 23 Feb 2015 17:46:51 GMT

=sum({$<FiscalYear=, MonthNum=, MonthNum = {'<=$(=Max({<FiscalYear = {$(=max(FiscalYear))}>}MonthNum))'}>}Metric)

UPDATED:

=SUM({<MonthNum = , MonthName=, FiscalYear=, MonthNum = {">=$(=Min({<FiscalYear = {'$(=Max(FiscalYear))'}>}MonthNum))<=$(=Max({<FiscalYear = {'$(=Max(FiscalYear))'}>}MonthNum))"}>}Metric)

Re: YTD issue

Anonymous — Mon, 23 Feb 2015 17:53:32 GMT

Thanks for the reply Manish,

when I make my selection as year = 2015,

then it just showing whole sum of metric from 2010 to 2015.

Regards

Sri1

Re: YTD issue

MK_QSL — Mon, 23 Feb 2015 17:54:04 GMT

=SUM({<MonthNum = , MonthName=, FiscalYear=, MonthNum = {">=$(=Min({<FiscalYear = {'$(=Max(FiscalYear))'}>}MonthNum))<=$(=Max({<FiscalYear = {'$(=Max(FiscalYear))'}>}MonthNum))"}>}Metric)

Re: YTD issue

Anonymous — Mon, 23 Feb 2015 17:58:13 GMT

Thanks Again Manish..

I already tried that expression but issue here is when a user selects 2015 and Month Feb it just calculate the metric value for month Feb ..but we need metric value to be summed from Sep to Feb (even for Month selection).

Thanks for Your patience

Sri1

Re: YTD issue

Anonymous — Mon, 23 Feb 2015 19:42:31 GMT

just figured out the expression actually we used variables as below

Sum({$<MonthName=,

MonthNum = {">=$(=vStartMonth)<=$(=vEndMonth) "}>} Metric)

vStartMonth =(max(FiscalYear)-1) * 12 -3

vEndMonth = Max({<FiscalYear = {'$(=Max(FiscalYear))'}>}MonthNum)

and in chart to YOY comparison by Month

I used this expression

=rangesum(Above( TOTAL sum({<Monthname =,FiscalYear = {$(=Max(FiscalYear))}>}Metric)

hope this helps others

Sri1