9 Replies Latest reply on Aug 28, 2015 3:32 PM by kelly.schmittel

# Sum sales using Parameter for # of weeks

I have a parameter created with list values for latest 4,13,26,52 weeks. How do I create a calculated field using that parameter input to return the sum of total sales for that specific time period? My data source is weekly sales data.

Thanks for the help.

• ###### 1. Re: Sum sales using Parameter for # of weeks

Kelly:

Create the param [Week Num] with the defined weeks and create a calc field as;

DATEPART('week', [Order Date]) <= [Week Num]

Place this on the filter shelf and select and Select True. With sum of sales in the view, this will show the sum of sales for the defined week numbers.

Hope this helps

..kk

• ###### 2. Re: Sum sales using Parameter for # of weeks

KK thanks for the reply. The answer you gave is only looking at the first few weeks in January for multiple years and not the LATEST weeks. My data also has multiple years in it.

Exactly what I'm looking for is this. The user selects the parameter value of 4. I need the total sales to return for the latest 4 weeks in the data(Aug. 2 - Aug 23). Alternatively if a user selects a value of 13 then I need the total sales for the latest 13 weeks(May 31 - Aug 23), etc. I would like to do this without the use of 'Today()' and have it use the latest week in the data set.

Thanks for any input.

• ###### 3. Re: Sum sales using Parameter for # of weeks

Hello Kelly,

If you are on V9, you can use an LoD to Derive the Max Week (It returns Row Level, so you can do the same kind of calculation as Today)

Best regards,

Rody

• ###### 4. Re: Sum sales using Parameter for # of weeks

If you aren't on V9, but your viz has Week of Date

Then you can use a calc like this

This won't "Filter" your data, but it will hide all weeks that are less than your Parameter setting.

Regards,

Rody

• ###### 5. Re: Sum sales using Parameter for # of weeks

Rody - Looks like this one worked. I am on Version 9.

Last part of this question. How can I change that to supply a YAGO TTL based on that same Parameter?

• ###### 6. Re: Sum sales using Parameter for # of weeks

Hi Kelly,

I'm not familiar with the term..

YAGO TTL

Regards,

Rody

• ###### 7. Re: Sum sales using Parameter for # of weeks

So you gave me the LATEST Parameter weeks which works great. How do I get the same thing for a Year Ago/Prior Year? So if Parameter value is a 4 then my result would be

Latest 4 weeks 2015        Latest 4 Weeks 2014

\$10,000                                 \$8,000

• ###### 8. Re: Sum sales using Parameter for # of weeks

Hello Kelly,

If you want to show the Same Time period between this year and last year...

Create two calculated Fields

[Current Year Sales] =>

END

[Previous Year Sales] =>

END

That should work.

Regards,

Rody

• ###### 9. Re: Sum sales using Parameter for # of weeks

Thank you sir. This is perfect.