Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik and ServiceNow Partner to Bring Trusted Enterprise Context into AI-Powered Workflows. Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Price Increases

RECEIPTS:
LOAD *,
 
if(num(Month(OrigReceiptDate))<=9,
Num(Month(OrigReceiptDate))+3,Num(Month(OrigReceiptDate))-9) as TrnMonth,
If(num(Month(OrigReceiptDate))<=9,
Year(OrigReceiptDate),Year(OrigReceiptDate)+1) as TrnYear;
LOAD Supplier,
OrigReceiptDate,
Year(OrigReceiptDate)&num(Month(OrigReceiptDate),00) as DateKey,
PurchaseOrder,
StockCode,
StockDescription,
QtyReceived,
MatchedValue
Where OrigReceiptDate >= '01/01/2013' and Warehouse<>'**' ;

I am loading the above data from my Purchase Order table the calculating the ave price for each TrnMonth and TrnYear in a table:

Current Period

Sum ({<TrnYear={$(=Max(TrnYear))},TrnMonth={$(=(VPeriod))}>} MatchedValue)/sum({<TrnYear={$(=Max(TrnYear))},TrnMonth={$(=(VPeriod))}>} QtyReceived)

Previous Period

Sum ({<TrnYear={$(=Max(TrnYear))},TrnMonth={$(=(VPeriod)-1)}>} MatchedValue)/sum({<TrnYear={$(=Max(TrnYear))},TrnMonth={$(=(VPeriod)-1)}>} QtyReceived)

and the same for the years by dropping the TrnYear from above. The table shows what prices increased/decreased.

What I want to do is flag the Supplier & Stockcode in my load if there is an increase during the month to enable any easy count of the number of increase/decreases by supplier and stock code. The same for the year averages.

Any suggestion how to achieve this?

Labels (1)
0 Replies