When to use set analysis

[karensmith]

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?

[Oleg_Troyansky · Accepted solution]

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

[johnw]

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

[karensmith]

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.

[karensmith]

Thanks once again Oleg!!!

[johnw]

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

[#7]

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

[johnw]

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.

[karensmith]

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?

f

'}>} 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'))