# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## Another question about date fields in QlikSense

I have a data set with a Date field and a field ‘VolumeConsumed’ per date.

DateStamp   VolumeConsumed

12/20/2015 - 40

12/21/2015 - 50

12/22/2015 - 50

12/23/2015 - 50

12/24/2015 - 50

12/25/2015 - 40

12/26/2015 - 30

I am trying to make a calculation and determine

1. Sum of VolumeConsumed in the past 3 days (in the example it is 120 (50+40+30)

2. Average VolumeConsumed in past 3 days (40)

3. The difference between average VolumeConsumed in past 3 days and Average VolumeConsumed (40 vs 44.3)

Thanks

Bjarke

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Another question about date fields in QlikSense

Hi Bjarke and Sangram,

I don't think these expressions could work with the Date field. In Set Analysis, Dates have to be properly formatted in order to be compared using Simple Search. For example, like this:

Sum({\$< DateStamp = {"<\$(=date(today() - 3))"} >}VolumeConsumed)

As an alternative, you can avoid formatting issues by using Advanced Search:

Sum({\$< DateStamp = {"=DateStamp<\$(=today() - 3)"} >}VolumeConsumed)

Also, the logical comparison in these formulas is reversed, compared to the requirement. To summarize the last three days, the Date should be greater than, not less than Today() -3:

Sum({\$< DateStamp = {">\$(=date(today() - 3))"} >}VolumeConsumed)

or

Sum({\$< DateStamp = {"=DateStamp>\$(=today() - 3)"} >}VolumeConsumed)

cheers,

Oleg Troyansky

8 Replies
MVP

## Re: Another question about date fields in QlikSense

All of this is required in a KPI object or a table?

Not applicable

## Re: Another question about date fields in QlikSense

In a table

Honored Contributor II

## Re: Another question about date fields in QlikSense

Hi Bjarke,

try these expressions: Make sure that the DateStamp field has been recognised as a date field.

1) Sum({\$< DateStamp = {"<\$(=today() - 3)"} >}VolumeConsumed)

2) Avg({\$< DateStamp = {"<\$(=today() - 3)"} >}VolumeConsumed)

3) Avg({\$< DateStamp = {"<\$(=today() - 3)"} >}VolumeConsumed) - Avg({1}VolumeConsumed)

Thanks,

Sangram Reddy.

Honored Contributor II

## Re: Another question about date fields in QlikSense

Hi Bjarke,

The above mentioned expressions work both in a KPI as well as in a Table.

MVP

## Re: Another question about date fields in QlikSense

Hi Bjarke and Sangram,

I don't think these expressions could work with the Date field. In Set Analysis, Dates have to be properly formatted in order to be compared using Simple Search. For example, like this:

Sum({\$< DateStamp = {"<\$(=date(today() - 3))"} >}VolumeConsumed)

As an alternative, you can avoid formatting issues by using Advanced Search:

Sum({\$< DateStamp = {"=DateStamp<\$(=today() - 3)"} >}VolumeConsumed)

Also, the logical comparison in these formulas is reversed, compared to the requirement. To summarize the last three days, the Date should be greater than, not less than Today() -3:

Sum({\$< DateStamp = {">\$(=date(today() - 3))"} >}VolumeConsumed)

or

Sum({\$< DateStamp = {"=DateStamp>\$(=today() - 3)"} >}VolumeConsumed)

cheers,

Oleg Troyansky

MVP

## Re: Another question about date fields in QlikSense

Sum({\$< DateStamp = {">=\$(=date(today() - 3))<=\$(=date(today()))"} >} VolumeConsumed)

Avg({\$< DateStamp = {">=\$(=date(today() - 3))<=\$(=date(today()))"} >} VolumeConsumed)

Avg(TOTAL {\$< DateStamp = {">=\$(=date(today() - 3))<=\$(=date(today()))"} >} VolumeConsumed)

-

Avg(TOTAL VolumeConsumed)

Not applicable

## Re: Another question about date fields in QlikSense

THis seems to do the job. Thanks for your help.

Honored Contributor II

Always welcome!