Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
All of this is required in a KPI object or a table?
In a table
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.
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)
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
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)
THis seems to do the job. Thanks for your help.
Always welcome!