Set Analysis to calculate the growth

[qlikviewforum]

I want a set analysis which calculate the growth in sales of current week when compared to the previous week. Also the set analysis should take when we are calculating the growth of first week sales of new year to the growth of last week sales of the previous year. Can you please provide some solution for this?

[Gysbert_Wassenaar]

To deal with weeks running over years you need to create a field in the script to get a serial weeknumber that doesn't reset per year. Your data needs to be ordered by week (or date) and then you can use the autonumber function to create a new week field: autonumber(Week,'WeekSerial') as WeekSerial. Or if you have a date field autonumber(weekstart(date),'WeekSerial') as WeekSerial.

If you want to use the week as dimension in a chart then you can't use set analysis. The set is calculated per chart, not per row. In that case see this document: Calculating rolling n-period totals, averages or other aggregations

If your going to select a week in a listbox and want to see the current and previous sales growth you can use something like:

current: sum({<WeekSerial={$(=max(WeekSerial))}>}SalesGrowth)

previous: sum({<WeekSerial={$(=max(WeekSerial)-1)}>}SalesGrowth)

[#3]

Hi,

Gysbert's answer is complete.

However, if you want to calculate the growth in the chart across weeks, you have to modify your data model because the set analysis is calculated once, for the whole chart.

I had published a document several weeks ago:

http://community.qlik.com/docs/DOC-4821

It may be useful in that case.

Fabrice

[#4]

Can you post an example QVW??

[qlikviewforum]

Thank you. How about using CurrentWeek= Max(WeekName) and PreviousWeek=Max(WeekName)-1.

Will this work?

[#6]

The minus 1 will work if the WeekName field is an integer, don't think it will for a string.

[qlikviewforum]

Yes WeekName is an integer here. After -1 again I am converting it to WeekName so that it matches with WeekName(Date).

[#8]

Then I can't see why that logic won't work, have you tried it?

[qlikviewforum]

Which logic you are talking about? WeekSerial Suggested by Gysbert?