topic Re: If statement/Set Anlysis - Show only current sales data in QlikView

If statement/Set Anlysis - Show only current sales data

Tue, 20 Oct 2015 16:44:52 GMT

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 — Tue, 20 Oct 2015 16:47:17 GMT

May be this:

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

swuehl — Tue, 20 Oct 2015 16:49:30 GMT

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 — Tue, 20 Oct 2015 16:51:20 GMT

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)

Tue, 20 Oct 2015 16:53:27 GMT

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

sunny_talwar — Tue, 20 Oct 2015 16:56:52 GMT

Awesome