topic Re: Need some help (SetAnalysis) in QlikView

Need some help (SetAnalysis)

Fri, 02 Mar 2012 15:48:07 GMT

Hi!

I need som help, I'm working on a document which should show the Sales Amount and Sales Total for Week1. of the period selected.

For example. If I choose the dates February 6th to February 26th it should show me the figures for week1 (6th till 12th) etc.

How do i do this?

I tried like this:

Sum({$<Week={">=$(=max(Week),-1)) <=$(=(max(Week)))"}>} PRICEOUT)

but that gives me the summary of the last week choosen.

Need some help (SetAnalysis)

Fri, 02 Mar 2012 23:20:19 GMT

Hello,

You want the lowest week of the period so you should use min() instead of max().

Martin

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 18:42:11 GMT

I tried with Sum({$<Week={">=$(=min(Week),-1)) <=$(=(min(Week)))"}>} PRICEOUT)

But the result is ju the summary of all the weeks before the one i choosen and the only thing i want is the last week not all.

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 19:17:27 GMT

I guess it depends on how you calendar is set up but you should only need to do this:

Sum({$<Week={"$(=min(Week))"}>} PRICEOUT)

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 19:32:12 GMT

Shouldnt it be a -1 somewhere to? (i am sorry but my set analysis isnt good at all need to study more)

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 19:45:30 GMT

Well that really depends how your calendar is set up. But if you are selecting dates and you want the week's data that the earliest date falls within then that should work perfectly assuming your weeks are numbers. Adding -1 would give you the week before the earliest date.

If you want the 7 days starting at the earliest date then you would need to do this instead:

Sum({$<Date={">=$(=min(Date)) <=$(=min(Date)+6)"}>} PRICEOUT)

I'd need to see the calendar in order to be sure though.

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 20:17:28 GMT

Nice! it seems to work! need to test it out some though, thank you very much! I'll tell you later how it went

One more thing, if i would like to also show the second week this would do it wouldnt it?

(Sum({$<Date={">=$(=min(Date)) <=$(=min(Date)+13)"}>} PRICEOUT))-(Column(17))

(it is working but i just want to check that this is no major fault to do)

Column17 is the one for the first week.

//BR
Patrik

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 20:34:47 GMT

Yup that would work or you could add 7 days to the start date rather than subtracting Column(17) like this:

Sum({$<Date={">=$(=min(Date)+7) <=$(=min(Date)+13)"}>} PRICEOUT)

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 20:37:32 GMT

Ah! smart! I'll do that! it looks cleaner!

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 20:41:51 GMT

Glad it worked! Don't forget to mark the solution to your question so that others with similar problems can find the answer easier.

Re: Need some help (SetAnalysis)

Sat, 03 Mar 2012 20:42:49 GMT

Will do! I'll just need to test it some more!

Thanks again!