Im working with a summary data set that essentially has tra...

[Douglas Slagowitz 3]

Im working with a summary data set that essentially has transactions stored by product, year and month with a detail column that tells me what pct of transactions were flagged for one reason or another

 

image.png947410 8.13 KB

 

What Im trying to do is create a HOLD FILE that will preserve the % Alerted column with the following logic:

if the row has the current year/month and there have been transactions in the current year/month

then keep the % Alerted and zero out any % Alerted for all other year/month combinations

If there were no transactions in the current year/month, then I want to keep the % Alerted from the prior month and if there were no transactions in the prior month, then keep % Alerted from the month before that until Ive looked back 3 months.

My current (broken) logic:

DEFINE FILE SUMMARY_RETAILER

PCT_FLG/D20.2 = IF DATA_YEAR EQ &CURRYEAR AND DATA_MONTH EQ &CURRMONTH AND TRANSACTION_COUNT GT 0 THEN PERCENT_FLAGGED

ELSE IF .DATA_YEAR EQ &CURRYEAR AND DATA_MONTH EQ &1CURRMONTH THEN PERCENT_FLAGGED ELSE 0;

END

This works for everything except for when theres transactions for both the current year/month and current year/previous month (note, I havent extended the logic to work back 3 previous months if no transactions are present in the current month and the 2 months prior, etc.) In this case, the % Alerted is kept for both, when I only want it kept for the most current month that has transactions.

Dizzying to explain, so Im open to answer any questions to help me figure this out.

[Warren Hinchliffe]

Im thinking a multiverb request to get the totals for product year and month to determine what gets kept, then a compute to reset the alert value.

(If I understand correctly)

[David Beagan]

If you get the rows in the proper order using BY fields then the LAST function can be used with COMPUTE fields to get previous row values, building up to previous three rows values. Putting it all together, something like the following code could work:

TABLE FILE SUMMARY_RETAILER

PRINT UNIT_OF_MEASURE_NAME

TRANSACTION_COUNT

PERCENT_FLAGGED

COMPUTE TRANSACTION_COUNT_1/I11 = LAST TRANSACTION_COUNT; NOPRINT

COMPUTE TRANSACTION_COUNT_2/I11 = LAST TRANSACTION_COUNT_1; NOPRINT

COMPUTE TRANSACTION_COUNT_3/I11 = LAST TRANSACTION_COUNT_2; NOPRINT

COMPUTE PERCENT_FLAGGED_1/P20.2 = LAST PERCENT_FLAGGED; NOPRINT

COMPUTE PERCENT_FLAGGED_2/P20.2 = LAST PERCENT_FLAGGED_1; NOPRINT

COMPUTE PERCENT_FLAGGED_3/P20.2 = LAST PERCENT_FLAGGED_2; NOPRINT

COMPUTE CURRENT/I1 = DATA_YEAR EQ &CURRYEAR AND DATA_MONTH EQ &CURRMONTH; NOPRINT

COMPUTE PCT_FLG/P20.2 = IF CURRENT AND TRANSACTION_COUNT GT 0 THEN PERCENT_FLAGGED ELSE

IF CURRENT AND TRANSACTION_COUNT_1 GT 0 THEN PERCENT_FLAGGED_1 ELSE

IF CURRENT AND TRANSACTION_COUNT_2 GT 0 THEN PERCENT_FLAGGED_2 ELSE

IF CURRENT AND TRANSACTION_COUNT_3 GT 0 THEN PERCENT_FLAGGED_3 ELSE 0;

QUANTITY_SOLD

BY PRODUCT_CATEGORY_NAME

BY DATA_YEAR

BY DATA_MONTH

END

[Douglas Slagowitz 3]

Thank you both for responding. I believe Ive gotten it to work using your technique Dave!

[Douglas Slagowitz 3]

So I wrote too soon. the LST function doesnt work here if there isnt a row for the current year / month.

Its getting the transaction count from a different product (the row above). Should I play around with adding a test to determine if its the same product during the compute for it Can I do the following:

COMPUTE TRANSACTION_COUNT_1/I11 = IF LAST PRODUCT_CATEGORY_NAME EQ PRODUCT_CATEGORY_NAME THEN LAST TRANSACTION_COUNT ELSE 0;

[Martin Yergeau]

Yes, this is what you can do

[John Gelona]

Douglas, You have to remember when things are done. DEFINEs operate on each input row where COMPUTE operate on the output of the Internal Matrix, after all sorting and summing. Look up the PARTITION_REF, PARTITION_AGGR and PREVIOUS functions.

[Toby Mills]

Hi Doug - I hope you guys are doing well!

I think Id suggest starting with the basic idea that if you get your data all lined up, youll be able to solve the problem.

What Id shoot for is to have 2 % alerted columns. One from the prior period and one from the current period. Start by displaying both of these columns in your report output so you can see them.

Then make your new column that decides which % Alert you want to show in your output. So youd end up columns something like

 

Then you either remove or just NOPRINT the Current_PCT and Prior_PCT columns and use COMPUTE to get your Display_PCT.

The main point really is to develop your report by showing more columns than you need to at first just so you can see whats going on. The rest will fall into place easier when you can see what WF is thinking.

Feel free to call if I can be of help. Kerrys got my number around there somewhere.

Toby

[Douglas Slagowitz 3]

Thanks Toby Hope youre doing well too.

I was able to solve it using Daves technique I had to tweak it a bit because of a little wrinkle. If there were no transactions for the current month, then take the PERCENT_FLAGGED for the most current of the 3 previous months that had a transaction. Just some additional IFTHENELSE logic.

[David Beagan]

Yes! I glad it worked for you.