Count Last 3 Months Identical Sale

[Anonymous]

Hi Guys,

I am trying to count Last 3 Months Identical Sales, and using below expression, but its not returning any thing.

Could you please advise me about this?

=Count(If(
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))>0
And
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))
=
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-1)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-1)),'YYYYMM'))"}>} Sale))
And
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))
=
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-2)),'YYYYMM'))"}>} Sale))
,1))

Kind regards,

Farrukh

[Anonymous]

Hi Sunny,

I think I need to make some change at designing side.

is there any possibility that I can do something when I load the data

LOAD SaleDate,
YearMonth,
Region,
CityCode,
ProCode,
District,
Sale,

If Last month (201610) Sale (200) = This Month (201611) Sale (200) Then "YES"    as  Identical_Sale

[sunny_talwar]

Hahahaha, I have no idea what you are trying to do my friend. I think we will need to take a step back and understand what exactly are you looking to get

[Anonymous]

Hi Sunny,

Sample data is attached.

As you can see the below table

Sep-2016 is a count of identical sale of last three month (its mean Jul Aug & Sep where the sale is same)

Aug-2016 is a count of identical sale of last three month (its mean Jun Jul & Aug where the sale is same)

Jul-2016 is a count of identical sale of last three month (its mean May Jun & Jul where the sale is same)

What I am trying to do is that I want to display

MonthYear | CityCode |  ProCode | Identical_Sale_Count

Sep-2016

Aug-2016

Jul-2016

Not in different expression. I want to display the count in one express.

[sunny_talwar]

So you would want to see 6 for WSZ_4 and 38 for WSZ_2 and so on?

[Anonymous]

Hi Sunny,

I want to see like this as in below table.

MonthYear	CityCode	ProCode	L3M Identical - Sale Count
Sep-16	City_8	WSZ_4	2
Sep-16	City_9	WSZ_2	12
Sep-16	City_11	WSZ_548	1
Sep-16	City_14	WSZ_140	1
Sep-16	City_15	WSZ_31	1
Sep-16	City_16	WSZ_462	4
Sep-16	City_16	WSZ_457	9
Sep-16	City_16	WSZ_463	2
Sep-16	City_16	WSZ_459	3
Sep-16	City_16	WSZ_458	27
Aug-16	City_8	WSZ_4	2
Aug-16	City_9	WSZ_2	13
Aug-16	City_11	WSZ_548	1
Aug-16	City_14	WSZ_140	1
Aug-16	City_15	WSZ_31	1
Aug-16	City_16	WSZ_462	4
Aug-16	City_16	WSZ_457	9
Aug-16	City_16	WSZ_463	2
Aug-16	City_16	WSZ_459	4
Aug-16	City_16	WSZ_458	28
Jul-16	City_8	WSZ_4	2
Jul-16	City_9	WSZ_2	13
Jul-16	City_11	WSZ_548	1
Jul-16	City_14	WSZ_140	1
Jul-16	City_15	WSZ_31	1
Jul-16	City_16	WSZ_462	4
Jul-16	City_16	WSZ_457	9
Jul-16	City_16	WSZ_463	2
Jul-16	City_16	WSZ_459	4
Jul-16	City_16	WSZ_458	29

[Anonymous]

Hi Sunny,

Do you think that it could be sorted at expression level?

or do I need to make some changes at scripting level.

Farrukh

[sunny_talwar]

I am checking it and will let you know

[Anonymous]

Thanks Sunny

[sunny_talwar]

Try this:

Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>}

  Aggr(

  If(

  floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)) > 0 and

  floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)) =

  Above(floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-3)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-1)),'YYYYMM'))"}>} Sale))) and

  floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)) =

  Above(floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-4)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-2)),'YYYYMM'))"}>} Sale)), 2), 1)

  , Region, CityCode, ProCode, District, MonthYear))

[Anonymous]

Hi Sunny,

Thanks a mil you have done a great help.

Could you please advise me how to manage this expression for last 12 Months? I tried to change it by -11 for last 12 months but its only returning the 3 months only.

Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date), -11 )),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>}

Farrukh