Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to calculate last 3months distinct outlet covered

Hi,

I am trying to get last 3 months distinct outlet covered in text object.by month wise values showing correct,but i want to display last 3months outlet count.

ex:- when i am selecting Jan month,i want to display Dec,Non,Oct outlent count.

i am using this expression,

=((count({$<YEAR = {$(=only(YEAR) - $(=if(only(MONTH) = 1, 1, 0)))}, MONTH = {$(=month(addmonths(MonthYear, -1)))},ClosingStock={'>0'}>}
distinct IF(AHC_CallDate = MX_DATE, AHCHdr_Outlet_Code))) --------this is for Dec
+
(count({$<YEAR = {$(=only(YEAR) - $(=if(only(MONTH) <= 2, 1, 0)))}, MONTH = {$(=month(addmonths(MonthYear, -2)))},ClosingStock={'>0'}>}
distinct IF(AHC_CallDate = MX_DATE, AHCHdr_Outlet_Code)))------------------this is for Nov
+
(count({$<YEAR = {$(=only(YEAR) - $(=if(only(MONTH) <= 3, 1, 0)))}, MONTH = {$(=month(addmonths(MonthYear, -3)))},ClosingStock={'>0'}>}
distinct IF(AHC_CallDate = MX_DATE, AHCHdr_Outlet_Code))))-----------------this is for Oct

by giving month wise it showing correct values.

can any one having idea about this,pls help me.

reg,

ba

3 Replies
Not applicable

How to calculate last 3months distinct outlet covered

hi,

Try using this Expression:

=count({1<Mon={$(=Month(addMonths(Date#(GetFieldSelections(Begindate),'DD-MM-YYYY'),-1)))}>}outlet_count)

Where begingdate is date and specify its format, Mon is month derrived from begindate

MVP
MVP

How to calculate last 3months distinct outlet covered

Hi,

Try this expression

=Sum(Aggr(count({$<MONTH=, DateFieldName={'>=$(MonthStart(Max(DateFieldName), -2))<=$(=Max(DateFieldName))}, ClosingStock={'>0'}>} distinct IF(AHC_CallDate = MX_DATE, AHCHdr_Outlet_Code)), MONTH))

Hope it helps you.

Regards,

Jagan.

Not applicable

How to calculate last 3months distinct outlet covered

Hi All,

Thanks for reply,This is not working,can any one know about this solution.Please help me.

Regards,

ba

Community Browser