Last 6 month no sale but with the min Date

[kavita25]

Hi,

I want those count whose sale last 6 month has no sale..but it should show for the min date...

For e.g

Dealer 1 has Sale for Nov and Dec

This dealer should show in the month of Nov and not in Dec...

My dimension is Month..So from Nov it should count for 6 months..not in previous months as my data is from May 2016.

stalwar1‌ gwassenaar‌- Please help me with this issue..

I tried this below logic...but no success...

=Count(

{<[DMS Outlet Code] =

     e({<InvFiscal_Month = {"=InvFiscal_Month=$(=Min(InvFiscal_Month))-1","=InvFiscal_Month=$(=Min(InvFiscal_Month))-2","=InvFiscal_Month=$(=Min(InvFiscal_Month))-3",

     "=InvFiscal_Month=$(=Min(InvFiscal_Month))-4","=InvFiscal_Month=$(=Min(InvFiscal_Month))-5","=InvFiscal_Month=$(=Min(InvFiscal_Month))-6"

     }>})

     *p({<InvFiscal_Month = {"=InvFiscal_Month=$(=Min(FirstYearOfSales))"}>} )

>}

DISTINCT  [DMS Outlet Code])

Regards,

Please help me with this issue...

Kavita

[kavita25]

The chart value is correct..that is 826 for the month of Nov...and so on..

but on the click it changes to 1817 that is the overall distinct count...fo rthe month of Nov...

It means after selection of Month the logic doesnt work.. rite?

[sunny_talwar]

Try this:

=Sum(Aggr(If(Only({<InvFiscal_Month>}InvFiscal_Month) = Min({<InvFiscal_Month>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) and

Min({<InvFiscal_Month>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) >= 8, Count(DISTINCT [DMS Outlet Code])), InvFiscal_Month, [DMS Outlet Code]))

[kavita25]

9140009500057 this code should come under Jan.. as it has sale on Jan and Jun...

But for last 6 months i.e. Jul,Aug,Sep,Oct,Nov, Dec has no sale.

Same for this code 9110015100396.. it has sale for May and Dec but not for 6 months..i.e Jun to Nov so this should come under Dec Month..

Regards,

Kavita

[sunny_talwar]

Does this resolve the issue

=Sum(Aggr(If(Only({<InvFiscal_Month>}InvFiscal_Month) = Min({<InvFiscal_Month = {'>=8'}>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) and

Min({<InvFiscal_Month = {'>=8'}>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) >= 8, Count(DISTINCT [DMS Outlet Code])), InvFiscal_Month, [DMS Outlet Code]))

[kavita25]

What if I want to start last 6 months from Aug...Nt from May...???

i.e. From Aug to Jan no sale and with min date...Feb has sale...

Is this will be logic??

=Sum(Aggr(If(Only({<InvFiscal_Month>}InvFiscal_Month) = Min({<InvFiscal_Month = {'>5<10'}>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) and

Min({<InvFiscal_Month = {'>5<10'}>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) >10, Count(DISTINCT [DMS Outlet Code])), InvFiscal_Month, [DMS Outlet Code]))

[sunny_talwar]

I am not sure... but if you know which months to check... a good approach would be to create flags in the script.

[kavita25]

Previous logic was for the different phase. that's correct..it started from May...

So for other phase, it started from Aug..

So i have to check same thing...but my starting date will be Aug..

[kavita25]

I used this logic to show last 6 months that starts from Aug:

The below logic doesnt work...

Sum(Aggr(If(Only({<InvFiscal_Month>}InvFiscal_Month) = Min({<InvFiscal_Month = {'>10'}>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) and

Min({<InvFiscal_Month = {'>10'}>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) > 10, Count(DISTINCT [DMS Outlet Code])), InvFiscal_Month, [DMS Outlet Code]))

But this logic works:

=Sum(Aggr(If(Only({<InvFiscal_Month>}InvFiscal_Month) = Min({<InvFiscal_Month>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) and

Min({<InvFiscal_Month>}TOTAL <[DMS Outlet Code]> InvFiscal_Month) > 10, Count(DISTINCT [DMS Outlet Code])), InvFiscal_Month, [DMS Outlet Code]))

can u please explain me..

[sunny_talwar]

I think Kavita we need to take a step back here.... Can you explain what exactly are you looking to get? May be explain when there are selections and when there are no selections made? Also, the previous logic is working in all cases? or do we need to amend something there as well?

[kavita25]

The previous logic is all correct...if my starting month is May...

But I want to show same thing but my starting date will be Aug not May..

As the business started from Aug...

9140015700021,9140015700027 this code has no sale for 6 months if I start from Aug to Jan..but has sale for

Feb (Min month)..

So this dealer code should be counted in Feb..month..Same I have to do for March...if there is no sale on Sep to Feb but has sale on March month (Min month) So it should come under March month...ad so on...