Display suppliers without purchase orders in the past 6 months (date selection)

[José_Espinoza]

Hi people!,

I'm trying to build a chart where I show data of suppliers with no purchase orders in the past 6 months. But that alone would be kinda lame, I was also thinking of adding the amount of purchase orders they had PRIOR to that 6 months period without purchase orders. This in a bar chart. So, I present the suppliers with no PO dimension, and a measure showing the amount of PO they had prior to that date.

I did it in script by adding flags, but that won't do if I want to work based on date selections, it's a bit static 😅. That's why I came tou you guys, to see if you can help me. I kinda understand the aggr function would work here, but as I'm not that good with it, I came hoping for some advice and help.

Thanks in advance.

[thi_pham]

I have an idea. Supposed that you have a dimension table of suppliers, then you can handle to add a new field in data load, or by a aggr dimension on GUI like:

last_purchase_order_date: aggr(max(purchase_order_date), supplier)

vMaxDate = max(Date) : to get the selected date. make sure the field of date is not the purchase_order_date to avoid your filter is also applied on the sum(purchase_order_date)

Then, when on chart you can measure like: 

 if (last_purchase_order_date < AddMonths($(vMaxDate), 6 ) , sum(purchase_orders_amount), null())

On chart, uncheck: show null() value on dimension

[ali_hijazi]

I've made something similar to your requirement
I displayed the customers with no sales in a selected period of time, with the last invoice date, last invoice number and last invoice amount

so you select a period of time and you want the suppliers with no order transactions in that period
your suppliers are in the Excluded set of data
I suggest that you send a sample file so that I can help you with your requirement but below is an expression is use to display the customers not served or with no sales in a selected period of time

so suppose you made a pivot table with Customer as dimension, your expression would look something like this:
suppose you want to display the last invoice number of customers with no sales
max(
{1
<
salesman_name=$::salesman_name,
company_name=$::company_name,
branch_customer.customer_code = E(
{
<
trx_type={'Sales'}
,branch_customer.customer_status = {'active'}
,branch_customer.customer_code = {"=Sum({<trx_type={'Sales'}>} amount_usd)>0"}
>
} branch_customer.customer_code)
,trx_type = {'sales'}
,branch_customer.customer_status={'active'}
,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=
,trx_date={'<=$(vEOM_LastMonth)'}
>
}invoice_number)