Get the sum of a deadline ignoring null values.

[#1]

Hi qlikview guys:

I want to get a value of my maximum date, but actually in my maximum date I don't have a value, it's null, and get the value of the maximum date where it has data, i want to skipthe null values.

I´m try with this set analysis but I was not lucky: sum({<Fecha_Abasto={'$(=max(Fecha_Abasto))'},Estatus_Abasto={'Abasto'}>}Estatus_Abasto)

Can any one help me?

Thanks!

[trdandamudi]

Your date column is a dimension, so can you check the "Suppress when value is  null" and see if you are getting the correct results...

[HirisH_V7]

Hi,

Try this,

sum({<Fecha_Abasto={'$(=max({<Estatus_Abasto={'Abasto'}>}Fecha_Abasto))'},Estatus_Abasto-={"-"}>}Conteo) 

HTH,

Hirish

[tamilarasu]

Hi Enrique,

As mentioned by Thirumala, you should enable the "suppress when value is null" option for the column "Estatus_Abasto" in dimension tab.

[sunny_talwar]

I think Hirish‌ your expression might run into two problems

1) You are using single quote within single quotes which might throw error

2) Fecha_Abasto is a date field and it would be better to format it using Date function here using DD/MM/YYYY as the format as it seems to be formatted like this from the image.

I would try like below

Sum({<Fecha_Abasto={"$(=Date(Max({<Estatus_Abasto={'Abasto'}>}Fecha_Abasto), 'DD/MM/YYYY'))"}, Estatus_Abasto={'Abasto'}>}Estatus_Abasto)

[trdandamudi]

Sunny,

Is it not it should be (Highlighted the field in Red):

Sum({<Fecha_Abasto={"$(=Date(Max({<Estatus_Abasto={'Abasto'}>}Fecha_Abasto), 'DD/MM/YYYY'))"}, Estatus_Abasto={'Abasto'}>} Conteo)

[sunny_talwar · Accepted solution]

Hahahaha yes, It would def. not be Estatus_Abasto because it is a text field. But if it is Conteo or something else, I won't know because Conteo may very well be the expression label and the actual field name is called totally different.

Sum({<Fecha_Abasto={"$(=Date(Max({<Estatus_Abasto={'Abasto'}>}Fecha_Abasto), 'DD/MM/YYYY'))"}, Estatus_Abasto={'Abasto'}>}Estatus_Abasto)

So I would ask the OP to replace Estatus_Abasto with whatever is being summed in the expression Conteo.

Thanks for pointing that out though

Best,

Sunny

[#8]

Thank you very much to all! The expression worked out correctly