topic Re: Set Analysis - Any Other Criteria With Fixed Date in QlikView

Set Analysis - Any Other Criteria With Fixed Date

Thu, 16 Apr 2015 14:55:08 GMT

I am attempting to show the 13 week average but want to allow any criteria selection to adjust this total (including the dates (i.e. users can look at last week or the previous compared to the 13 week avg)). I think the answer is close but my troubleshooting is going in circles.

I have the 13 weeks set. This appears complex but I only want to show the last 13 weeks in an environment which has future ship dates.

sum({<ShipCalWeekStart = {'>=$(=WeekStart(Max({1<ShipDate = {'<=$(=today())'}>} ShipCalWeekStart),-13))<=$(=WeekStart(Max({1<ShipDate = {'<=$(=today())'}>} ShipCalWeekStart),-1))'}>} ShipmentTotal)

If I add The Identifier {1} to look at the complete application as I want for the dates. I then lock out the many criteria sections I wish users to use.

sum({1<ShipCalWeekStart = {'>=$(=WeekStart(Max({1<ShipDate = {'<=$(=today())'}>} ShipCalWeekStart),-13))<=$(=WeekStart(Max({1<ShipDate = {'<=$(=today())'}>} ShipCalWeekStart),-1))'}>} ShipmentTotal)

What am i missing to allow for Criteria 1,2,3....to show their 13 week avg?

Appreciate the help.

Re: Set Analysis - Any Other Criteria With Fixed Date

sunny_talwar — Thu, 16 Apr 2015 15:07:50 GMT

Do you want the expression to always choose the max week regardless of the selection made? If not, what if you use this:

sum({<ShipCalWeekStart = {'>=$(=WeekStart(Max({<ShipDate = {'<=$(=today())'}>} ShipCalWeekStart),-13))<=$(=WeekStart(Max({<ShipDate = {'<=$(=today())'}>} ShipCalWeekStart),-1))'}>} ShipmentTotal)

1 is limiting your results after selection. If a particular selection does not land up between your 13 day period, you would see a 0 as output.

HTH

Best,

Sunny

Re: Set Analysis - Any Other Criteria With Fixed Date

sunny_talwar — Thu, 16 Apr 2015 18:17:05 GMT

Did this finally worked out? What was going wrong, I am curious.

Sunny

Re: Set Analysis - Any Other Criteria With Fixed Date

Thu, 16 Apr 2015 18:43:56 GMT

Cleaning up the thread. This is not resolved. Original Post still applies.

Re: Set Analysis - Any Other Criteria With Fixed Date

sunny_talwar — Thu, 16 Apr 2015 18:46:05 GMT

So its not working again . What exactly is the issue. Can you share a sample?