topic Re: Set Analysis - Sales > Fiscal Week in QlikView

Set Analysis - Sales > Fiscal Week

Tue, 14 Jan 2014 03:45:26 GMT

Hi,

I am trying to write an expression to only show average sales in the latest fiscal year from Fiscal WK25 onwards, based on the week selection in my filter.

I have my expression below however I can't seem to make it dynamic - I want the sales to change based on my Fiscal Week selection. ie. If I select Wk 25, 26 it will show the average sales for these two weeks. If I select Wk 28-31 it will show me the average sales for these 4 weeks. At the moment the below will show me the average of ALL sales greater than Wk24 regardless of what week I select.

({$<[Fiscal Year]={$(=max([Fiscal Year]))},{$<[Fiscal Week]={$(">24")}>}[Net Sales])

Sorry - I'm a Qlikview beginner and still getting used to Set Analysis.

Thanks,

Bronwyn

Re: Set Analysis - Sales > Fiscal Week

VishalWaghole — Tue, 14 Jan 2014 05:08:15 GMT

Hi Bronwyn,

Try with this expression, Hope it will work for you.

=sum({<Date= {">=$(=Date(Max(WeekName))) <=$(=Max(Date))"}>}Sales)

- Regards,

Vishal Waghole

Re: Set Analysis - Sales > Fiscal Week

jonathandienst — Tue, 14 Jan 2014 05:11:11 GMT

Perhaps this:

({$<[Fiscal Year]={$(=max([Fiscal Year]))},{$<[Fiscal Week]*={$(">24")}>}[Net Sales])

(intesection of selection and set expression value)

HTH

Jonathan

Re: Set Analysis - Sales > Fiscal Week

Wed, 15 Jan 2014 00:19:21 GMT

Thanks Jonathan, I think your expression above is missing a } but I'm not sure where to add it. Do you add an extra >} before Net Sales?

({$<[Fiscal Year]={$(=max([Fiscal Year]))},{$<[Fiscal Week]*={$(">24")}>}[Net Sales])

Re: Set Analysis - Sales > Fiscal Week

jonathandienst — Wed, 15 Jan 2014 08:32:28 GMT

Bronwyn

Actually I just copied yours and added * to the *=. But looking at the expression more carefully, I can see a couple of problems - try this:

({$<[Fiscal Year]={$(=max([Fiscal Year]))}, [Fiscal Week]*={">24"}>} [Net Sales])

HTH

Jonathan

Re: Set Analysis - Sales > Fiscal Week

Wed, 15 Jan 2014 13:40:28 GMT

Hi Bronwyn, You already made week selection 28-31 and we want to see all the weeks from 24-31 then you need to remove the week selection from set analysis:

({$<[Fiscal Year]={$(=max([Fiscal Year]))},{$<[Fiscal Week]=,[Fiscal Week]={">24<=$(=Max([Fiscal Week]))"}>}[Net Sales])