topic Re: Sum Expression (within a given date range) in QlikView

Sum Expression (within a given date range)

akuligod — Wed, 25 Nov 2020 16:16:04 GMT

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

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

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

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

>} DISTINCT Design_Parts)

aarkay29 — Tue, 07 Aug 2018 18:58:30 GMT

what are CampainDate_YYYYMM and CampainDateAfter_YYYYMM

fieldnames/Variables?

akuligod — Tue, 07 Aug 2018 19:03:46 GMT

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 — Tue, 07 Aug 2018 19:06:28 GMT

Try This

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

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

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

>} DISTINCT Design_Parts)

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

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

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

>} DISTINCT Design_Parts)

akuligod — Tue, 07 Aug 2018 19:13:26 GMT

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.

aarkay29 — Tue, 07 Aug 2018 19:17:10 GMT

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

akuligod — Tue, 07 Aug 2018 19:27:25 GMT

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

Count of parts before and after campaign date