topic Set Anlysis Syntax: Last Week's Shipments in QlikView

Set Anlysis Syntax: Last Week's Shipments

Thu, 16 Aug 2012 18:20:01 GMT

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!

Re: Set Anlysis Syntax: Last Week's Shipments

Jason_Michaelides — Thu, 16 Aug 2012 18:43:02 GMT

Try:

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

Hope this helps,

Jason

Re: Set Anlysis Syntax: Last Week's Shipments

Jason_Michaelides — Thu, 16 Aug 2012 18:44:48 GMT

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.

Re: Set Anlysis Syntax: Last Week's Shipments

Thu, 16 Aug 2012 20:55:33 GMT

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?

Re: Set Anlysis Syntax: Last Week's Shipments

Jason_Michaelides — Thu, 16 Aug 2012 20:59:38 GMT

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

Re: Set Anlysis Syntax: Last Week's Shipments

Thu, 16 Aug 2012 21:03:05 GMT

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

Thank you!!!

Re: Set Anlysis Syntax: Last Week's Shipments

stevedark — Thu, 16 Aug 2012 21:03:53 GMT

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/