topic When to use set analysis in QlikView

When to use set analysis

karensmith — Sun, 30 Aug 2009 04:08:05 GMT

I inventory application based on monthly selection. If user select current month then saels and inventory is summed but, there one set of data that may not have month values. This data is Point of sales data. Usually this data is available until the middle of the current month for previous month.

With that being said if user is using appliation in Aug Point of sales values will be zero. I need to the chart to pull the previous month value if there is not current month data, but if current month data is available then use current month value.

How can I handle this? Do I need to use Set Analysis. If yes, what syntax?

When to use set analysis

Oleg_Troyansky — Mon, 31 Aug 2009 00:14:55 GMT

I'd suggest to solve the last question in the load script - determine what is the most recent available data (current month or prior month) and load it as the "Latest Available Number".

Oleg

When to use set analysis

johnw — Tue, 01 Sep 2009 01:28:01 GMT

I would handle it in the load script as well.

If you prefer to handle it with an expression, then probably something like this (untested, and may need to be changed a bit based on your field names and definitions):

if (Month=max({1}Month)
and sum("Point of Sales")=0
,sum({<Month={'$(=date(addmonths(max({1}Month),-1),'MMM YY'))'}>}"Point of Sales")
,sum("Point of Sales"))

When to use set analysis

karensmith — Thu, 03 Sep 2009 10:11:15 GMT

Thanks for the recommendation John. I would prefer to handle this on the user interface, but your recommend syntax did not work. I didn't know you could set analysis in a if statement....interesting...when selction for previous month is selected from the listbox value is displayed, but when current month is selected no values are displayed.

When to use set analysis

karensmith — Thu, 03 Sep 2009 22:46:36 GMT

Thanks once again Oleg!!!

When to use set analysis

johnw — Fri, 04 Sep 2009 02:49:09 GMT

It looks like the problem with my expression was that I needed the "total" keyword in several spots. Here's the revised expression, and see attached for it working in context:

if (Month=max({1} total Month)
and sum("Point of Sales")=0
,sum({<Month={'$(=date(addmonths(max({1} total Month),-1),'MMM YY'))'}>} total "Point of Sales")
,sum("Point of Sales"))

When to use set analysis

Fri, 04 Sep 2009 03:24:26 GMT

John,

If no data was available in the prior month, could a loop be inserted into the SA part to grab the last known value whenever that might be?

Sincerely - Robt

When to use set analysis

johnw — Fri, 04 Sep 2009 05:17:58 GMT

Well, I'm not thinking of a way to insert a loop. Again, fairly straightforward in the script, as you'd just load in month order, and grab the previous month's value if the current month you're loading is 0. You can mimic the same thing in the chart with the above() function. But I like to avoid that, because it literally takes what is above it in the chart, so if you make selections or change the sort order, it will change what is displayed.

Anyway, here are those two approaches added to the example along with some more missing data to show the differences.

When to use set analysis

karensmith — Fri, 04 Sep 2009 20:08:16 GMT

John,

Thanks for going back and updating expression. I did download your application, but not sure what to truly expect. It seem to work for one or two selections, but then only show the show based on what was truly selected.

When I retype your syntax I get no results. I ideally I would like the use to select month and based on selction calculate the previous or current month. FY month is not a field in my chart. I tried it both ways, but still nothing. What am I missing?

'}>} total POS_Qty),sum(POS_Qty))

(FYMonth=max({1} total FYMonth) and sum(POS_Qty)=0,sum({<FYMonth={'$(=date(addmonths(max({1} total FYMonth),-1),'MMM YY'))