Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

delbooth
New Contributor II

Dates from excel

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

6 Replies
MVP
MVP

Re: Dates from excel

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

dwforest
Valued Contributor

Re: Dates from excel

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
Contributor III

Re: Dates from excel

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
New Contributor II

Re: Dates from excel

Thanks very much

delbooth
New Contributor II

Re: Dates from excel

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
Valued Contributor

Re: Dates from excel

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