Qlik Community

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
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)

You can learn more about using Set Analysis with Simple Search and Advanced Search in my new book QlikView Your Business. Or, you can listen to my lecture about Set Analysis and Advanced Aggregation at the Masters Summit for Qlik.

cheers,

Oleg Troyansky

8 Replies
MVP
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

reddys310
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.

reddys310
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
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)

You can learn more about using Set Analysis with Simple Search and Advanced Search in my new book QlikView Your Business. Or, you can listen to my lecture about Set Analysis and Advanced Aggregation at the Masters Summit for Qlik.

cheers,

Oleg Troyansky

MVP
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.

reddys310
Honored Contributor II

Re: Another question about date fields in QlikSense

Always welcome!