Question 174313 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Qlik Sense: Understanding what this summation expression does

I’m very new to qlik sense – I have some database experience.
A team mate of mine recently quit who was the main owner of this piece of code.

num(sum({1<[report_date] = {"=$(=(date((currDate))))"}>
    +1<[report_date]={"=$(date(currDate) - 7)"}>
    +1<[report_date]={"=$(date(currDate) - 30)"}>
    } anomoly_count ) / 100 )

Could someone please help me understand what it’s trying to do.
My understanding its summing up the values of these 3 days

val on currDate + val on (currDate - 7 days) + val on (curr - 30 days).

What I’m trying to achieve is
val on currDate + average value of last 7 days + average value of last 30 days.

I tried writing the database query equivalent for the above qlik sense query and the results do not match.
I.e the value rendered on the qlik sense dashboard vs the value I see on database select statement.

sum_count AS (
SELECT SUM(anomoly_count) as sum_anomoly_count
FROM "summary"
WHERE
report_date::date IN (‘2023-05-11’, ‘2023-05-04’, ‘2023-04-11’)
AND anomoly_count IS NOT NULL
)

SELECT (sum_count.sum_anomoly_count ::NUMERIC / 100) as result
FROM sum_count

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    this isn't the complete solution - but it's a good starting point.

    num(
    sum({1<[report_date] = {">=$(=(date((currDate) - 30))) <=$(=(date((currDate) - 7)))"}>} anomoly_count )
    + sum({1<[report_date] = {">=$(=(date((currDate) - 7))) <$(=(date((currDate))))"}>} anomoly_count )
    + sum({1<[report_date] = {"=$(=(date((currDate))))"}>} anomoly_count )
) / 100

  2. 0

    TLDR

    I think this is what you’ll want:

    (Avg(Aggr(Sum({1<[report_date] = {">=$(=(date((currDate) - 30))) <=$(=(date((currDate) - 7)))"}>} anomoly_count ), [report_date]))
    + Avg(Aggr(Sum({1<[report_date] = {">=$(=(date((currDate) - 7))) <$(=(date((currDate))))"}>} anomoly_count ), [report_date]))
    + Sum({1<[report_date] = {"=$(=(date((currDate))))"}>} anomoly_count )
)
/ 100

    Quick note on why that should work

    That expression uses the Aggr() function to get the sum of [anomoly_count] averaged on each [report_date] value. It appears you had the rest of that set up correctly, at least based on your parameters.

    A deeper look at the Qlik concepts involved

    Your coworker was using a feature of Qlik expressions called Set Analysis — a very simple way of thinking about it is as a Where clause for expressions used in charts. Another way of describing it would be that you are sort of "baking in" selections into an expression. Set analysis (also "set expressions") are placed at the beginning of aggregation functions, like Sum() or Avg().

    In your example, this would be the aggregation function:

    sum( anomoly_count )

    …and then this would be the set analysis part:

    {1<[report_date] = {"=$(=date(currDate))"}>
    +1<[report_date] = {"=$(=date(currDate - 7))"}>
    +1<[report_date] = {"=$(=date(currDate - 30))"}>
    }

    Let’s note that we are also using the concepts of variables and dollar-sign expansion in this example. We have a variable, currDate, that is presumably set to the current day with the Today() or Now() function. If we tried to just use currDate by itself in between those double quotes, it wouldn’t do anything because Qlik will just see a token or string — we want to access the actual value of the currDate variable, so we need to use dollar-sign expansion to do so.

    Here, we’re also using the Date() function to format today’s date as the default date format (otherwise, Qlik may display a 5 digit number which is still technically a date but it won’t do us any good that way in this case).

    Another point to make here — the ones 1 that are in front of each line are known as a Set Identifier, which tells Qlik whether to take into account the selections that a user may make. The ones 1 basically mean "ignore all selections made outside of this expression." This is important because by itself, this…

    {<[report_date] = {"=$(=date(currDate))"}>

    …would mean "where [report_date] is the value of today’s date," but if a user or another expression in the chart were to select a [report_date] value of 100 days ago, this expression would return an empty set because by default we take into account those records that the user has selected. However, if we add our 1 to the beginning again…

    {1<[report_date] = {"=$(=date(currDate))"}>

    …then this expression will only show the records where [report_date] is today’s date, regardless of what else has been selected.

    The final consideration is the use of the set operators, which in this case are those plus signs + separating the set expressions. In a set expression, the plus sign is a union operator, or a big OR in a way.

    Finally, here’s what your example is actually saying:

    "Give me the sum of [anomoly_count] where [report_date] is today’s date, 7 days ago, or 30 days ago, regardless of any user selections."

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search