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

[kfoudhaily]

Hello,

try the expression bellow, i'm only not sure with the part where i repeat the set on the same field ItemReviewDate.

try to test how expression acts when you just remove this part ItemReviewDate={">$(cSame_Month_1_yr_Ago_yyyymm)"},

please check orthography, It would have been easier if you have made me just copy the fields hh

Count(

          {$<

          DataType={'Registration'},

          ItemStatus={'Approved','Expired'},

          ItemReviewDate={">$(cSame_Month_1_yr_Ago_yyyymm)"},

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

          >}

          DISTINCT Design_Parts_Regs

          )

regards,

[akuligod]

Not working. Tried taking out >cSame_month.

Have pasted the fields for convenience. Can you suggest any other changes?

Count({<

DataType={'Registration'},

ItemStatus={'Approved','Expired'},

ItemReviewDate_YYYYMM={">$(cSame_Month_1_yr_Ago_yyyymm)"},

ItemReviewDate_YYYYMM={"=ItemReviewDate_YYYYMM<CampaignDate_YYYYMM" AND "=ItemReviewDate_YYYYMM>CampaignDateBefore_YYYYMM"}

>}DISTINCT Design_Parts_Regs)

[vishsaggi]

May be this?

= Count({<

DataType={'Registration'},

ItemStatus={'Approved','Expired'},

ItemReviewDate_YYYYMM={">$(=cSame_Month_1_yr_Ago_yyyymm)"},

ItemReviewDate_YYYYMM = {">$(=CampaignDateBefore_YYYYMM)<$(=CampaignDate_YYYYMM)"}

>} DISTINCT Design_Parts_Regs)

[akuligod]

Both of the above work. Sorry had overlooked a parenthesis.

Now trying to do similar for the 'after' the campaign date period.

The results do not seem convincing.

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

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

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

          >} DISTINCT Design_Part_Mapping_AK)

[vishsaggi]

Use = in side the parenthesis.

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

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

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

          >} DISTINCT Design_Part_Mapping_AK)

[akuligod]

using = inside the parenthesis does not work.

Can you please suggest any other changes?

Thanks in advance.

[kfoudhaily]

Hello,

please provide qvw sample if possible.

[vishsaggi]

Would it be possible to upload your qvw file?

[akuligod]

Sorry, not allowed to share the data. I have only an example snippet attached.

The 'before' formula seems to be working okay. The 'after' is all showing zeros.