Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
akuligod
New Contributor

Sum within a date range

1.PNG

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

16 Replies
Highlighted
kfoudhaily
Contributor III

Re: Sum within a date range

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,

Highlighted
akuligod
New Contributor

Re: Sum within a date range

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)

Highlighted
vishsaggi
Esteemed Contributor III

Re: Sum within a date range

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)

Highlighted
akuligod
New Contributor

Re: Sum within a date range

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)

Highlighted
vishsaggi
Esteemed Contributor III

Re: Sum within a date range

Use = in side the parenthesis.

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

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

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

          >} DISTINCT Design_Part_Mapping_AK)

Highlighted
akuligod
New Contributor

Re: Sum within a date range

using = inside the parenthesis does not work.

Can you please suggest any other changes?

Thanks in advance.

Highlighted
kfoudhaily
Contributor III

Re: Sum within a date range

Hello,

please provide qvw sample if possible.

Highlighted
vishsaggi
Esteemed Contributor III

Re: Sum within a date range

Would it be possible to upload your qvw file?

Highlighted
akuligod
New Contributor

Re: Sum within a date range

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.

2.PNG