Set Analysis - Sales > Fiscal Week

[#1]

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

[VishalWaghole]

Hi Bronwyn,

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

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

- Regards,

Vishal Waghole

[jonathandienst]

Hi

Perhaps this:

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

(intesection of selection and set expression value)

HTH

Jonathan

[#4]

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]) 

[jonathandienst]

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

[#6]

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])