Set expression in a Dimension (Date) for a static Bar chart

[463]

TIA!

I am trying to use a bar chart to show the sales of the last six weeks for many stores.  Left axis would be sales, bottom axis would be stores and bars would be the Monday's for the past six weeks corresponding to when the week's data was loaded.  I want the dates to be static in that the user can't change them, but, upon every new week, we see the previous six weeks of data.

I was trying to use a set expression in the 'bar' dimension, but it kept coming back as 'invalid.'  I can determine the previous six Mondays and place them into a string delimited by a character ('/').  Or use the date functions and work back six Mondays and use that.  Doesn't matter to me.

The stores would be dynamic based on region drilling down to states, cities, etc.  The left axis would represent a few measures.  The bars are what I want as static.

What would be a solution?  

[WeLoveQlik · Accepted solution]

Hello! 

The first thing i would do is create a weekday Field in the script using the WEEKDAY() Function.

Then I would create a Variable that you can use to reference the last 6 weeks.

if you have a Weekindex Field, you could use Max(Week Index)-6 and then use Set analysis

New Variable 

        v6WeeksAgo = Max(WeekIndex)-6

Set Analysis

{< WeekIndex= {">$(=$(v6WeeksAgo ))"}, WeekDay = {'Mon'} >}

If you only have Dates, I would create a variable that is your Max( Date ) - 42 and use that in set analysis.

New Variable 

        v6WeeksAgo = Max(Date)-42

Set Analysis

{< Date = {">$(=$(v6WeeksAgo))"}, WeekDay = {'Mon'} >}

[marcus_sommer]

In addition to the suggestions from @WeLoveQlik such conditions such be placed within the expressions and not in the dimensions.

Expressions are native aggregations which could be simply extending with set statements. By dimensions it would be needed to use either if-loops for the conditions or to wrap the dimensions with an aggr() construct to apply a set statement - and both ways a rather more complex and coming with disadvantages in regard to the usability and performance. 

[463]

Thanks to all.

It took a little finagling but these post showed me the light.