Sum within a date range

[akuligod]

trying to get the above formula to work.

a. doing a count within a date range (3 months before to campaign date)

b. check all the date formats for consistency.

c. there is error in line 5 (on second to last line..looks like the 'and' condition is not working)

d. please suggest if I am missing any rules.

Kindly help me this formula to work or suggest similar alternatives.

Thank you,

Ajay

[akuligod]

Count({$<DataType={'Registration'},

          RegItemStatus={'Approved','Expired'},

          RegItemReviewDate_YYYYMM={">$(=CampainDate_YYYYMM)<$(=CampainDateAfter_YYYYMM)"}

          >} DISTINCT Design_Part_Mapping_AK)

using the above mentioned formula for 'after' range.

Thanks for all your help on this.

[akuligod]

stalwar1‌ jagan‌ swuehl‌

Experts can you help on this?

[sunny_talwar]

Not sure, but may be this

Count({$<DataType={'Registration'}, RegItemStatus={'Approved','Expired'}, Design_Part_Mapping_AK ={"=RegItemReviewDate_YYYYMM < CampainDate_YYYYMM and RegItemReviewDate_YYYYMM > CampainDateAfter_YYYYMM"}>} DISTINCT Design_Part_Mapping_AK)

[akuligod]

The above formula does not work. Have similar formula for before that works.

Count({$<DataType={'Registration'},

          RegItemStatus={'Approved','Expired'},

          RegItemReviewDate_YYYYMM={">$(CampainDateBefore_YYYYMM)<$(CampainDate_YYYYMM)"}

          >} DISTINCT Design_Part_Mapping_AK)

[sunny_talwar]

How is your data structured do you have a single RegItemReviewDate_YYYYMM, CampainDate_YYYYMM and CampainDateAfter_YYYYMM for each Design_Part_Mapping_AK?

Would you be able to share some mock up data and the expected output from the mocked up data

[akuligod]

Inner Join (Fact)

LOAD Region,

     DistributorName,

     [Campaign Date],

     Date([Campaign Date],'YYYYMM') AS CampaignDate_YYYYMM,

     Date([Campaign Date],'YYYY') AS CampaignDate_YYYY,

     Date(AddMonths([Campaign Date],3,0),'YYYYMM') as CampaignDateAfter_YYYYMM,

     Date(AddMonths([Campaign Date],-3,0),'YYYYMM') as CampaignDateBefore_YYYYMM,

     CampaignDate_YYYYWK,

     [Campaign Name],     

     Product_ID,

     [In Part List]

multiple campaigns have campaign dates. Want to do a count of design_parts that occurred before & after (3 month window) the campaign date.  Design_parts is continuous data stream by reg_item_date

Hope this helps. Sorry cant share a lot of details.

[sunny_talwar]

I am not looking for your confidential data, but mocked up data to make sense of your actual data...

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample