topic Re: Sum within a date range in QlikView

Sum within a date range

akuligod — Mon, 06 Aug 2018 16:43:38 GMT

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

Re: Sum within a date range

kfoudhaily — Mon, 06 Aug 2018 17:50:47 GMT

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,

Re: Sum within a date range

akuligod — Mon, 06 Aug 2018 19:17:30 GMT

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)

Re: Sum within a date range

vishsaggi — Mon, 06 Aug 2018 19:28:00 GMT

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)

Re: Sum within a date range

akuligod — Mon, 06 Aug 2018 19:40:31 GMT

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)

Re: Sum within a date range

vishsaggi — Mon, 06 Aug 2018 20:03:34 GMT

Use = in side the parenthesis.

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

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

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

>} DISTINCT Design_Part_Mapping_AK)

Re: Sum within a date range

akuligod — Mon, 06 Aug 2018 20:21:55 GMT

using = inside the parenthesis does not work.

Can you please suggest any other changes?

Thanks in advance.

Re: Sum within a date range

kfoudhaily — Mon, 06 Aug 2018 20:45:44 GMT

Hello,

please provide qvw sample if possible.

Re: Sum within a date range

vishsaggi — Mon, 06 Aug 2018 20:47:42 GMT

Would it be possible to upload your qvw file?

Re: Sum within a date range

akuligod — Mon, 06 Aug 2018 20:52:59 GMT

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.

Re: Sum within a date range

akuligod — Mon, 06 Aug 2018 20:55:58 GMT

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.

Re: Sum within a date range

akuligod — Thu, 09 Aug 2018 16:27:23 GMT

stalwar1‌ jagan‌ swuehl‌

Experts can you help on this?

Re: Sum within a date range

sunny_talwar — Thu, 09 Aug 2018 16:31:23 GMT

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)

Re: Sum within a date range

akuligod — Thu, 09 Aug 2018 16:36:31 GMT

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)

Re: Sum within a date range

sunny_talwar — Thu, 09 Aug 2018 16:41:26 GMT

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

Re: Sum within a date range

akuligod — Thu, 09 Aug 2018 16:49:00 GMT

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.

Re: Sum within a date range

sunny_talwar — Thu, 09 Aug 2018 16:50:56 GMT

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