Adjust Expression to Calculate Prior Year Values

[#1]

Hi there,

I'm trying to write an expression that will return the number of sales leads created from the same day last year (day-of-week adjusted). This is what I have so far:

Sum ({$<date_activity={'=$(=Date(Today(date_activity)-364))'}>} leads_created)

To be honest, I'm so new to QlikView that I don't really know what this formula even says...I just copied it from another thread and added the '-364' to try to adjust the Activity Date ('date_activity').

If today's date in my DB is 6-26-12, and I want leads created from 6-30-11 (364 days ago), how would I write the expression? I want this to be a rolling calculation, so hard-coding a date won't work...

Thanks so much!

Stephen

[MayilVahanan]

HI,

     Try this,

     =Sum({<date_activity = {"=Date(Max(date_activity)-364)"}>}leads_created)

Hope it helps

[#3]

Hi Mayil,

Thanks for trying to help; unfortunately that formula returns the current sum of leads created, not the same-day last year.

Stephen

[MayilVahanan]

Hi,

     Try this,

     =Sum({$<date_activity={'$(=Date(Max(date_activity)-364))'}>}leads_created)

     See the below link, it helps for set analysis problem

    http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=_CN...

     Hope it helps you

[chematos]

=Sum({<date_activity = {"=AddMonths(Date,-12)"}>}leads_created)

Date could be any date or Today() if you want. You make sure about the format of date, you could use Date(Date,'DD/MM/YYYY') or Date(Today(),'DD/MM/YYYY') instead only Date inside the function AddMonths.

Hope this helps

[#6]

Thanks! I found this tool a little while ago but then it 'moved'...glad to have it again!

[fernando_tonial]

Hi,

You can try this.

=Sum({$<date_activity={'$(=AddYears(Max(date_activity),-1))'}>} leads_created)

Hope this helps

Best Regards.

Tonial

[#8]

No, unfortunately that returns ‘0’…thanks for trying to help, I appreciate it!

Stephen Williams

Financial Analyst

A Place For Mom, Inc.

1300 Dexter Ave N

Seattle, WA 98109

Phone: 206-802-1612

E-Mail: stephenw@aplaceformom.com<mailto:stephenw@aplaceformom.com>

Website: www.aplaceformom.com<http://www.aplaceformom.com/>