Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead 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
Oleg_Troyansky

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

View solution in original post

8 Replies
sunny_talwar

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

Not applicable
Author

In a table

reddy-s
Master II
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.

reddy-s
Master II
Master II

Hi Bjarke,

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

Oleg_Troyansky

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

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

reddy-s
Master II
Master II

Always welcome!