I want matched code for particular month and previous to previous month...

[kavita25]

I want those count where the code has sale for particular month and previous to previous month...not for previous month...

For.e.g- Jul  = Jul+May(Matching code)

            Aug = Aug+Jun

            Sep = Sep+Jul    

            Oct  = Oct+Aug

and so on...

Regards,

Kavita

[tresesco]

PFA

Created two fields for month and year and used expression like:

=Count({<[Invoice Year],[Invoice Month],[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]))))  <=$(=Date(MonthEnd(Max([Invoice Date]))))"}>

  +

  <[Invoice Year],[Invoice Month],[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]),-2)))  <=$(=Date(MonthEnd(Max([Invoice Date]),-2)))"}>

  }[Invoice No.])

[tresesco]

If you are trying to count the Common (between those two months) Outlet Code, then try like:

=Count(DISTINCT{<[Invoice Year],[Invoice Month],[Invoice No.]=p({1<[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]))))  <=$(=Date(MonthEnd(Max([Invoice Date]))))"}>})>

  *

  <[Invoice Year],[Invoice Month],[Invoice No.]=p({1<[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]),-2)))  <=$(=Date(MonthEnd(Max([Invoice Date]),-2)))"}>})>

  }[Outlet Code])

[kavita25]

Thank You for your reply...

Will it work with Month as dimension??

[kavita25]

tresesco‌

Its not working with month as Dimension..

Kinldy look into this...

[tresesco]

Share the qvw that you tried with and also explain your expected output there.

[kavita25]

Hi tresesco‌

I want those Outlet code who has done sale for

Jul & May... will be counted for the month of Jul,

Aug & Jun will be counted for the month of Aug,

Sep & Jul will be counted for the month of Sep

Oct & Aug will be counted for the month of Oct

For e.g 9110012202617 will be counted for the Month of Jul, Sep, Oct and not in Aug as it doesnt has sale for the month of Jun.

9110015100033 will be counted only for the month of Jul...

Also, i have tried ur logic..but it's showing different counts...the count should be distinct for 1 outlet code.

Regards,

Kavita

[tresesco]

Try with this correction (in bold):

=Count(DISTINCT{<[Invoice Year],[Invoice Month],[DMS Outlet Code]=p({1<[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]))))  <=$(=Date(MonthEnd(Max([Invoice Date]))))"}>})>

  *

  <[Invoice Year],[Invoice Month],[DMS Outlet Code]=p({1<[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]),-2)))  <=$(=Date(MonthEnd(Max([Invoice Date]),-2)))"}>})>

  }[DMS Outlet Code])

[kavita25]

I tried ur logic...

When I am selecting one outlet code it should show me 1 as count, but its showing me different counts.

Please check the image

[tresesco]

Well, remove set modifier {1} from p() like:

=Count(DISTINCT{<[Invoice Year],[Invoice Month],[DMS Outlet Code]=p({<[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]))))  <=$(=Date(MonthEnd(Max([Invoice Date]))))"}>})>

  *

  <[Invoice Year],[Invoice Month],[DMS Outlet Code]=p({<[Invoice Date]={">=$(=Date(MonthStart(Max([Invoice Date]),-2)))  <=$(=Date(MonthEnd(Max([Invoice Date]),-2)))"}>})>

  }[DMS Outlet Code])

and select Year 2016 to see your desired output, because when nothing is selected it takes the max year-month (which is of 2017) and for max year-month the condition is false.