topic Re: MTD & LTD in set analysis in QlikView

MTD & LTD in set analysis

Wed, 01 Jan 2014 16:24:39 GMT

Hi All,

Happy new year to everyone!!!!!

I have req. as below and I need some help in this.

I have certain fields like Product,number,values,id,date,cal_month fields.

I need to calculate the MTD and LTD formulae’s with respect selection and without selection in front end level.

If I am selecting the particular month it has to show the values with that particular month like values upto that month(Live Till Date) and values(MTD i.e only that particular month) in one chart
2.If am not selecting any list box it has to values LTD(till the date) and MTD(only current month) in one chart.

Plz find the attached sample data and I am unable to attach the original data.

Thanks in advance,

Ravi

Re: MTD & LTD in set analysis

nirav_bhimani — Wed, 01 Jan 2014 18:03:04 GMT

Try this,

LTD:
= sum(
{<
Revenue_Date= {">=$(=Date(YearStart(min({1}Revenue_Date))))<=$(=max(Revenue_Date))" }, Revenue_Week= , Revenue_Quarter=, Revenue_Month= >}
GrossRevenue)

MTD:

= sum(
{<
Revenue_Date= {">=$(=Date(MonthStart(Max(Revenue_Date))))<=$(=max(Revenue_Date))" }, Revenue_Week= ,Revenue_Year=, Revenue_Quarter= >}
GrossRevenue)

Regards,

Nirav Bhimani

Re: MTD & LTD in set analysis

iktrayanov — Wed, 01 Jan 2014 20:16:24 GMT

Use pivot table with two expressions

MTD:

sum(Value)

LTD:

rangesum(above(sum(Value),0,RowNo()))

See Attached