Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
akuligod
Contributor
Contributor

Sum Expression (within a given date range)

The formula below does not seem to work. Kindly suggest corrections.

  • trying to sum campaign data from campaign date to 3 months later.
  • checked all date formatting.

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

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

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

          >} DISTINCT Design_Parts)

6 Replies
aarkay29
Specialist
Specialist

what are CampainDate_YYYYMM and CampainDateAfter_YYYYMM

fieldnames/Variables?

akuligod
Contributor
Contributor
Author

these field names. Trying to get a sum/count of parts 3 months after the campaign date.

LOAD Region,

     CompanyName,

     [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,

aarkay29
Specialist
Specialist

Try This

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

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

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

          >} DISTINCT Design_Parts)

or

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

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

          RegItemReviewDate_YYYYMM={">$(=Date(Max(CampainDate_YYYYMM),'YYYYMM'))<$(=Date(Max(CampainDateAfter_YYYYMM),'YYYYMM'))"}

          >} DISTINCT Design_Parts)

akuligod
Contributor
Contributor
Author

the above formula does not seem to work.

  • there are several campaigns and every campaign has unique date.
  • trying to sum before/after for every campaign
  • give me sum of all parts with RegItemreview date greater than campaign date but less that CampaignDateAfter (3 months). For every  campaign.

4.PNG

aarkay29
Specialist
Specialist

Can you please share the qvw with some sample data or post some sample data & how you want see that

akuligod
Contributor
Contributor
Author

Sorry cant share the qvw. Here is what I expect to see.

Count of parts before and after campaign date

5.PNG