If statement/Set Anlysis - Show only current sales data

[#1]

We have products that move through warehouses.   I want to only show products which have sales in the last 6 months.

If I just use the ProductNo as a dimension, I get all the products that have had sales in that warehouse.

I am trying to do a calculated dimension along these lines (tried both ways) to only show products with active sales.

=If(SUM({$<Date_InvoiceDate={">=$(=monthstart(date(today()-180)))"} >} Invoice_SalesAmount)>0, ProductNo)

=SUM({$<Date_InvoiceDate={">=$(=monthstart(date(today()-180)))"} >} if(Invoice_SalesAmount>0, ProductNo))

[sunny_talwar · Accepted solution]

May be this:

If(Aggr(Sum({$<Date_InvoiceDate={">=$(=MonthStart(Date(Today()-180)))"} >} Invoice_SalesAmount), ProductNo) > 0, ProductNo)

[swuehl]

If you want a calculated dimension:

=aggr(

If(SUM({$<Date_InvoiceDate={">=$(=monthstart(date(today()-180)))"} >} Invoice_SalesAmount)>0, ProductNo) ,      ProductNo

)

Or using ProductNo as dimension and a set expression to filter expressions:

=SUM({$< ProductNo = {"=Sum( {<Date_InvoiceDate={'>=$(=monthstart(date(today()-180)))'} >} Invoice_SalesAmount)>0"} >} Incoice_SalesAmount)

[settu_periasamy]

Hi,

did you try the same expression with suppress zero value in presentation tab

(Dimension : Product)

=SUM({$<Date_InvoiceDate={">=$(=monthstart(date(today()-180)))"} >} Invoice_SalesAmount)

[#5]

Yes - Sunny's example with the Suppress Null worked like a charm.

[sunny_talwar]

Awesome