Dates from excel

[delbooth]

Hi

I am trying to only sum dates for a gauge chart that are for the year 2018, the column of data has data from 2016 to 2018.  The data format is 04/03/2018  00:00:00.  Do I need to reformat the data or can Qlik view identify the year and use that in the sum expression.

Thnaks

[petter]

If the Excel column contains what is truly dates it is no problem for Qlik to read them as dates however the format.

[dwforest]

Depends on your timestamp format value.

SET TimestampFormat='MM/DD/YYYY HH:mm:ss';

This would match the value supplied, then you would use Year(datefield) = 2018, if for a Set Expression, you would either need to have date connected to a Master Calendar (highly) recommended so you could do:

    Sum({<Year={2018}>} valuefied)

without you need a more complex expression against a primarykey field:

    Sum({<IDfield=("=Year(datefield)=2018"}>} valuefield)

[isingh30]

If you need only date then use floor function & get rid of time. After that you can sum up either using if statement or set analysis.

[delbooth]

Thanks very much

[delbooth]

I have tried the above responses.  Essentially I am looking to use a KPI visualization to display the amount of material collected in 2018.  When I use the set timestamp comment in the KPI data element it says an error, does the set timestamp need to be put elsewhere on Qlik sense desktop

[dwforest]

The SET statements are at the top of your load script.