Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- above() function and date selection

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

davdiste

Partner - Creator

2019-08-01
06:28 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

above() function and date selection

Hi,

Consider the table presented below.

Data Load | Quantity | Category |

31/10/2018 | 74.170 | apple |

31/10/2018 | 3.264.618 | orange |

31/10/2018 | 7.487.132 | pear |

30/11/2018 | 79.892 | apple |

30/11/2018 | 3.329.629 | orange |

30/11/2018 | 7.318.647 | pear |

31/12/2018 | 85.071 | apple |

31/12/2018 | 3.382.092 | orange |

31/12/2018 | 7.162.199 | pear |

31/01/2019 | 91.648 | apple |

31/01/2019 | 2.515.989 | orange |

31/01/2019 | 7.923.292 | pear |

28/02/2019 | 95.574 | apple |

28/02/2019 | 3.459.038 | orange |

28/02/2019 | 6.862.316 | pear |

31/03/2019 | 99.109 | apple |

31/03/2019 | 3.499.973 | orange |

31/03/2019 | 6.753.454 | pear |

Starting from the selection of the dates of the year 2019 (e.g.) I need to calculate the __difference__ between the sum of the "quantity" of a date and the sum of the "quantity" of the date immediately above. The second table display the expected result.

Data Load | Difference Quantity (data n) - (data n-1) |

31/01/2019 | -98433 |

28/02/2019 | -114001 |

31/03/2019 | -64392 |

I've tried to solve using different formulas based on the function ABOVE without success. The issue is related with the selection applied on the dimension Data Load, indeed selecting the dates of the year 2019 the function ABOVE doesn't consider the not selected dates computing the difference and in particular the last date "excluded" in the selection (see the attachment).

=(sum( [Quantity]) - Above(sum( [Quantity])))

Even trying to using set analysis aimed to ignore the selection of the data load dimension didn't reach the desired result.

Does someone have any suggestion?

thanks

Davide

- Tags:
- qlik app

2,372 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2019-08-01
08:11 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try this

```
=RangeSum(
Sum([Quantity]),
-Above(Sum({<[Data Load]>}[Quantity]))
) *
Avg(1)
```

8 Replies

sunny_talwar

MVP

2019-08-01
08:11 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try this

```
=RangeSum(
Sum([Quantity]),
-Above(Sum({<[Data Load]>}[Quantity]))
) *
Avg(1)
```

davdiste

Partner - Creator

2019-08-01
10:04 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Sunny,

I didn't understand completely why, but works!

I've improved the formula in case of selection of other temporal dimension.

=RangeSum(

Sum({<[Category]=-{'apple'}>}[Quantity]),

-Above(Sum({<[Data Load],[Year],[Month],[Category]=-{'apple'}>}[Quantity]))

) *

Avg(1)

2,346 Views

sunny_talwar

MVP

2019-08-01
10:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Superb... the only other thing I would change is to add the set analysis from your main expression to the Avg(1) expression

```
=RangeSum(
Sum({<[Category]=-{'apple'}>}[Quantity]),
-Above(Sum({<[Data Load],[Year],[Month],[Category]=-{'apple'}>}[Quantity]))
) *
Avg({<[Category]=-{'apple'}>} 1)
```

davdiste

Partner - Creator

2019-08-01
10:33 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

thanks again.

I didn't understand the contribute of the "Avg" function, may I ask you an explanation?

and why should work better adding the condition

Avg({<[Category]=-{'apple'}>} 1)

thx

Davide

2,334 Views

sunny_talwar

MVP

2019-08-01
12:01 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Basically, by adding the set analysis to ignore selection in Data Load, Year, Month fields you are telling your expression to show everything regardless of selection in those fields.... now how do you force non selected items to go away? For this we use Avg(1).... Avg(1) = 1 for selection and Avg(1) = 0 for non selections....

Asking to add the same set analysis to Avg... because you want Avg() to work for the same selections where it works for your main expression....

Does it make sense?

davdiste

Partner - Creator

2019-08-02
05:45 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

yes is smart, I've understood decomposing the formula.

Just another question regarding the set analysis. I've been used to consider group operator like the following

Sum({<[Category]={'Apple'}>}[quantity]))

in your formula I've noticed this syntax (e.g.):

Sum({<[Category]>}[quantity]))

In this case, what is the function of the operator without the = ?

Thanks

Davide

2,303 Views

sunny_talwar

MVP

2019-08-02
12:42 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That just means that I am ignoring selection in Category field... Similar to {<[Category] = >}

Rehan

Creator III

2020-03-21
11:25 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How can we use this expression in a KPI object or Text Object?

1,953 Views