MTD & LTD in set analysis - Qlik Community

Report Inappropriate Content · ‎2014-01-01

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

Anonymous · ‎2014-01-02

Hi,

Please check attached.

Hope this helps.

Regards,
Aadil

VishalWaghole · ‎2014-01-02

Hi

You can try this, Hope it will helpful for you..

LTD:
= sum({< Revenue_Date= {">=$(=Date(YearStart(min(Revenue_Date))))<=$(=max(Revenue_Date))" }>}GrossRevenue)

MTD:

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

- Regards,

Vishal Waghole

brijesh1991 · ‎2014-01-02

Create a variable

vMonth = Max(Date); where Date is your calender date

For LTD,

count({$<Set Analysis if anything you have>}if (InMonthToDate ( Revenue_Date, '$(vMonth)', 0), CallID))

For MTD:

vMonthEnd= date(floor(monthend('$(vMonth)')), 'DD/MM/YYYY')

count

({$<Set Analysis if anything you have>}if (InMonthToDate ( Revenue_Date, '$(vMonthEnd)', 0), CallID))

stevedark · ‎2014-01-02

Hi,

You may find these examples helpful for prior period comparison using Set Analysis:

- Steve