Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
So you would want to see 6 for WSZ_4 and 38 for WSZ_2 and so on?
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 |
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
I am checking it and will let you know
Thanks Sunny
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))
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