Set Anlysis Syntax: Last Week's Shipments

[#1]

I'm having a problem with Set Analysis. I'm trying to display last week's shipments, but the following expression is returning zeros:

Sum({< FISCAL_END_DATE = {'=$(=Date(FISCAL_END_DATE)-7))'} >} SELLIN_SHIPMENT_QUANTITY)

Can anyone spot a syntax error? I always struggle w/placement of quotes, parantheses & brackets.

Thank you!

[Jason_Michaelides]

Try:

Sum({< FISCAL_END_DATE = {"$(=Date(FISCAL_END_DATE)-7)"} >} SELLIN_SHIPMENT_QUANTITY)

Hope this helps,

Jason

[Jason_Michaelides]

Oops -

Sum({< FISCAL_END_DATE = {"$(=Max(FISCAL_END_DATE)-7)"} >} SELLIN_SHIPMENT_QUANTITY)

Near in mind this approach is no good if FISCAL_END_DATE is a dimension in your chart.

[#4]

Thanks for the help but it is still not working.

FISCAL_END_DATE is also a dimension in my chart. It allows me to show current week shipments but I wanted to put last week shipments next to it. Is this even possible? Do I have to create a table to link this week & last week in my load script for this to work?

[Jason_Michaelides · Accepted solution]

Thought so - hence my last note.  Set Analysis only evaluates once per chart so is no good against changing dimensions within a chart.  Try:

Sum(SELLIN_SHIPMENT_QUANTITY) as the first expression and

Above(Sum(SELLIN_SHIPMENT_QUANTITY)) as the second.

Hope this helps,

Jason

[#6]

It worked brilliantly...actually used Below because of my sort order. Nice feature! I haven't used that before

Thank you!!!

[stevedark]

Hi there - you do not need to have the FISCAL_END_DATE as a dimension in the chart.  Just two expressions one with the end date tied to the max end date with Set Analysis and the other two the date seven days prior.

Your code looks largely correct for achieving this, but something that you do need to watch with set analysis on dates is that the format of the value coming out of the variable needs to match the natural format of the date field.  For example, you may need to do something like:

Sum({< FISCAL_END_DATE = {"$(=Date(Max(FISCAL_END_DATE)-7), 'DD/MM/YYYY')"} >} SELLIN_SHIPMENT_QUANTITY)

I tend to go via variables for set analysis like this, so create one for max fiscal end and one for prior as this can make syntax a bit simpler.

I have uploaded a couple of Set Analysis examples to Shared QlikViews that may be of help:

http://community.qlik.com/qlikviews/1175

http://community.qlik.com/qlikviews/1113

Good luck!

Steve

http://www.quickintelligence.co.uk/