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

[sunny_talwar · Accepted solution]

You would need to Add Aggr between Count and if and use your dimensions

[nagarjuna_kotha]

Can u please share sample data along with output .

It would better i guess

-Nagarjun

[Anonymous]

Thanks Sunny.

[Anonymous]

Hi Sunny,

Can you please see the attached sample data, as per your advised I used the dimension with Aggr function and it was working but due to some reason its just returning zero values.

I am trying to count Last Three month Identical sale. By displaying MonthYear Dimension for last 7 months.

Can you please advise me about this?

Kind regards,

Farrukh

[sunny_talwar]

Your YearMonth field is not read as date here. It seems to be read as a text or number... Try this in the script:

LOAD SaleDate,

  Date(MonthStart(Date#(YearMonth, 'YYYYMM')), 'YYYYMM') as YearMonth,

    Region,

    CityCode,

    ProCode,

    District,

    Sale

FROM

(ooxml, embedded labels);

$(Include=F:\TestChartCalendar2.txt);

[Anonymous]

Thanks Sunny,

I change the format in excel file and its sorted.

As you can see the I have a count of identical sale of last three month and Current month is selected Sep Year is 2016.

Could you please advise me how to display count of last 6 months.

Aug-2016

Jul-2016

Jun-2016

[sunny_talwar]

May be this:

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

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

And

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

=

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

And

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

=

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

, 1), Region, CityCode, ProCode, District))

[Anonymous]

Hi Sunny,

Its returning a count just for one month, and other two months count are zero.

Farrukh

[sunny_talwar]

Not sure what you required output is, but isn't this what you want?