# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
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

1 Solution

Accepted Solutions

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

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

Not applicable
Author

In a table

Master II

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.

Master II

Hi Bjarke,

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

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

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
Author

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

Master II

Always welcome!