Set analysis over year end

[#1]

Hi,

I have been trying to write a chart that lets the user select a week and then displays the previous 8 weeks sales, I have managed to this by using

sum({<Cal_Week={$(=Only(Cal_Week))}>} [Sales Value])

sum({<Cal_Week={$(=Only(Cal_Week)-1)}>} [Sales Value]) and so on,

unfortunately this does not work if you want to cross over years, I thought I could solve this by counting the weeks I have data for and using this to go back

sum({<Cal_WeekCount={$(=Only(Cal_WeekCount))}>} [Sales Value])

sum({<Cal_WeekCount={$(=Only(Cal_WeekCount)-1)}>} [Sales Value]) and so on,

unfortunately I did not fully understand Set Analysis and did not realise it only worked on the selected field and not any related fields (both Cal_Week & Cal_WeekCount are in my calendar table).

Any advice on how to do this would be helpful since almost all of our data is stored in weeks and I could see this as being a regular problem for me

Thanks,

Gavin.

[#2]

Hi Gavin,

You could create a new field in your calendar table that is a combination of week and year. ie 200852. You could then use this new field in your set analysis expression.

Kind Regards

Footsie

[#3]

Hi,

I could create this new field but I cant see how this would help me, the user would still need to be able to select a week not a value on this new field so I would be in the same position as I am now trying to use Cal_WeekCount, unless I am misunderstanding what you are saying .

[#4]

Do you have a fiscal year field? You can try this:

sum({<Cal_WeekCount={$(=Only(Cal_WeekCount))},FiscalYear={'$(=GetFieldSelections(FiscalYear))'} >} [Sales Value])
sum({<Cal_WeekCount={$(=Only(Cal_WeekCount))}, FiscalYear={'$(=GetFieldSelections(FiscalYear)-1)'} >} [Sales Value])

[#5]

'Thanks for the reply but that did not work either ...

I guess what I am actually asking is can I select one field in a table and use Set analysis on a separate field in that table to determine what information to display.

[johnw]

You can list all the fields you want to ignore in the expression, then handle the weeks as a range instead of as individual weeks. Also add a Cal_Year_Week field as Footsie suggested, and make it an actual date field, such as weekname(Cal_Date) as Cal_Year_Week. Then do something like this (untested):

sum({<Cal_Date=,Cal_Week,Cal_WeekCount=, // and so on for all related date fields
,Cal_Year_Week={">$(=weekname(max(Cal_Year_Week)-8*7) <=$(=max(Cal_Year_Week))"}>} "Sales Value")

[#7]

John,

Thanks for the reply, this is well out of my experience of QlikView , I was hoping that it was a simple process of counting backwards using the week count that was created in the script, I will try to understand your suggestion and give it a go.

Am using

sum({1<Cal_WeekCount={$(=Only(Cal_WeekCount)-1)}>} [Sales Value])

for now and will have to manage without further selection until I understand it better !!

Ta,

Gavin.

[johnw]

Maybe I'm not understanding your data. I assumed that Cal_WeekCount restarted at 1 at the beginning of each year. If it instead starts at 1 with the first week of data you have, and counts up from there without rolling back to 1 for each new year, yes, you can use that instead of a date field. Your sum({1..) says that you want to ignore ALL fields, which simplifies things as well, as you don't then have to list which fields you want to ignore. That leaves us with something like this:

sum({1<Cal_WeekCount={">$(=max(Cal_WeekCount)-8) <=$(=max(Cal_WeekCount))"}>} "Sales Value")

[#9 · Accepted solution]

Thanks to everyone who replied,

By taking sections from each proposed solution I was able to achieved my desired result. Once again thank you for taking the time to help, from your examples I feel that I now have a slightly better (still lots to learn though !!) understanding of the set analysis functions that QlikView has to offer.

Gavin.