topic Re: Adjust Expression to Calculate Prior Year Values in QlikView

Adjust Expression to Calculate Prior Year Values

Wed, 27 Jun 2012 16:59:59 GMT

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

Re: Adjust Expression to Calculate Prior Year Values

MayilVahanan — Wed, 27 Jun 2012 17:28:10 GMT

HI,

Try this,

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

Hope it helps

Re: Adjust Expression to Calculate Prior Year Values

Wed, 27 Jun 2012 17:30:50 GMT

Hi Mayil,

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

Stephen

Re: Adjust Expression to Calculate Prior Year Values

MayilVahanan — Wed, 27 Jun 2012 18:59:31 GMT

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=_CNGH

Hope it helps you

Re: Adjust Expression to Calculate Prior Year Values

chematos — Wed, 27 Jun 2012 19:41:10 GMT

=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

Re: Adjust Expression to Calculate Prior Year Values

Wed, 27 Jun 2012 19:47:15 GMT

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

Re: Adjust Expression to Calculate Prior Year Values

fernando_tonial — Thu, 28 Jun 2012 02:40:03 GMT

Hi,

You can try this.

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

Hope this helps

Best Regards.

Tonial

Re: Adjust Expression to Calculate Prior Year Values

Thu, 28 Jun 2012 22:06:10 GMT

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/>