Result of sum is Null in advanced Search

[#1]

Hello everyody

I need to make an advanced search inordet to configure an alert.

The definition of the search is te following:

I want to show only the TIENDAS (stores) that belong to the GRUPO_COMERCIO = Grupo 1 and not have sells (TOT_COMISION_COMPARTIDA) Yesterday.

And I have the following expression in order to have the yesterday's sales:

=sum( {$<[GRUPO_COMERCIO]={'GRUPO 1'},FECHA = {">=$(=Date(today(1)-1))"},AñoD= ,TrimestreD= ,MesD= ,QuincenaD= ,SemanaDim= ,SemanaD= >} TOT_COMISION_COMPARTIDA)=

When I put this expression in the advance search of the fiel TIENDA, telling the sum = 0

=sum( {$<[GRUPO_COMERCIO]={'GRUPO 1'},FECHA = {">=$(=Date(today(1)-1))"},AñoD= ,TrimestreD= ,MesD= ,QuincenaD= ,SemanaDim= ,SemanaD= >} TOT_COMISION_COMPARTIDA)=0

The result is nothig (I suppose becasue for these stores there are no transacciotns to sum). But when I change the = 0 for >0, the result show me the stores that have sales yestarday.

How Can I seach for the stores that not have sells yesterday.

Thanks in advance for your help

[ramoncova06]

did you have any store with no sales yesterday at all ?

[#3]

Yes I have some of them

[ramoncova06]

so let's troubleshoot this,

select yesterday date, create a table with Tienda as a dimension and sum(TOT_COMISION_COMPARTIDA) as a Metric, uncheck suppress zero values from the presentation tab

[sunny_talwar]

Do a advanced search like this:

=not Sum( {$<[GRUPO_COMERCIO]={'GRUPO 1'},FECHA = {">=$(=Date(today(1)-1))"},AñoD= ,TrimestreD= ,MesD= ,QuincenaD= ,SemanaDim= ,SemanaD= >} TOT_COMISION_COMPARTIDA) > 0

[#6]

Ok

Only show me the Stores with transacctions and I need just exacly the oposite ones.

[#7]

Doesn't work...the search dont give me anithing

[sunny_talwar]

I tried with not on one of the application I am working on and it seems to work, not sure if this is due to null values in there. I am going to work on a sample application and see if I can get it to work.

Best,

Sunny

[sunny_talwar]

Try this advanced search and see if this helps:

=Sum(TOT_COMISION_COMPARTIDA) - Sum( {$<[GRUPO_COMERCIO]={'GRUPO 1'},FECHA = {">=$(=Date(today(1)-1))"},AñoD= ,TrimestreD= ,MesD= ,QuincenaD= ,SemanaDim= ,SemanaD= >} TOT_COMISION_COMPARTIDA) =Sum(TOT_COMISION_COMPARTIDA)

I am attaching a sample application where I tested it out and seems to be working.

Script:

Table:

LOAD * Inline [

Year, Store, Sales

2014, 1, 50

2015, 1, 45

2014, 2, 30

2015, 3, 54

2015, 4, 57

];

Advanced Search: =Sum(Sales) - Sum({<Year = {2014}>}Sales) = Sum(Sales)

Try it out and may be this can help.

Best,

Sunny

[sunny_talwar · Accepted solution]

You want to go even simpler than that, you can try this:

=1 - Sum( {$<[GRUPO_COMERCIO]={'GRUPO 1'},FECHA = {">=$(=Date(today(1)-1))"},AñoD= ,TrimestreD= ,MesD= ,QuincenaD= ,SemanaDim= ,SemanaD= >} TOT_COMISION_COMPARTIDA) = 1