topic Re: sum based on two dates in QlikView

sum based on two dates

Wed, 25 Nov 2020 16:16:04 GMT

= sum({<Date={">=$(vStartDate)"}*{"<=$(vEndDate)"}>} Actuals)

The above equation shows the correct value when i click on each and every product. But when I click on Brand wise, it is not providing the correct value. The date range is not working if i click on two products at the same time.

Am I missing any aggregation for date? Any suggestions will be appreciated.

Re: sum based on two dates

sunny_talwar — Wed, 20 Jul 2016 13:00:27 GMT

How do you define vStartDate and vEndDate?

Re: sum based on two dates

hic — Wed, 20 Jul 2016 13:02:45 GMT

How are the variables defined? How are Products linked to Date and Actuals?

You need to post an example app.

HIC

Re: sum based on two dates

Wed, 20 Jul 2016 13:02:50 GMT

vStartDate = Date(aggr(Date_of_Product),product_key)

vEndDate=Addmonths(vstartDate,2)

Re: sum based on two dates

Anonymous — Wed, 20 Jul 2016 13:06:50 GMT

I guess that your two products have different startdates

can you check for each product what startdate is displayed (create textobject with vStartdate)

Re: sum based on two dates

Clever_Anjos — Wed, 20 Jul 2016 13:07:48 GMT

You need an aggregation inside your AGGR() function

Max()? Min()?

Date(aggr( here Date_of_Product),product_key)

Re: sum based on two dates

Wed, 20 Jul 2016 13:08:37 GMT

Yes you are right the two products having different startdates so in that case, how I'm able to get the sum of Actuals of two products. Any idea.

Re: sum based on two dates

Wed, 20 Jul 2016 13:10:18 GMT

First I used min (startdate) and I can achieve the correct results. But the requirement is like different dates for every product.

For a particular brand I have different products with different dates. So for overall - how I can achieve the result?

Re: sum based on two dates

Anonymous — Wed, 20 Jul 2016 13:10:36 GMT

use min or max

= sum({<Date={">=min($(vStartDate))"}*{"<=max($(vEndDate))"}>} Actuals)

not sure if Syntax is correct

Re: sum based on two dates

Clever_Anjos — Wed, 20 Jul 2016 13:10:49 GMT

As hic‌ techs us in https://community.qlik.com/blogs/qlikviewdesignblog/2015/10/05/pitfalls-of-the-aggr-function

1. Missing inner aggregation function

The first parameter of the Aggr() is a measure, and as such it is always an aggregation. Hence, you should use an aggregation function. If you don’t, the calculation will use Only() and the virtual table may inadvertently get NULLs in the Measure column. See more on Use Aggregation Functions!

Re: sum based on two dates

Wed, 20 Jul 2016 13:11:26 GMT

try below:

vStartDate = Date(min(aggr(min(Date_of_Product),product_key)))

Re: sum based on two dates

Anonymous — Wed, 20 Jul 2016 13:12:18 GMT

I guess you Need min max in variable

vStartDate = min(Date(aggr(Date_of_Product),product_key))

vEndDate=Addmonths(vstartDate,2)

then you can Keep the expression

= sum({<Date={">=$(vStartDate)"}*{"<=$(vEndDate)"}>} Actuals)

Re: sum based on two dates

Wed, 20 Jul 2016 13:17:51 GMT

The requirement is like different date for different product so I have removed the min from the variable. Now I can get the value for every product but when i click on Brand it is not giving the proper summation. Date difference is not working perfectly.

So where I have to use the aggregation exactly to resolve the issue. I have tried aggr() but its not working. Is there any solution for this issue?

Re: sum based on two dates

Clever_Anjos — Wed, 20 Jul 2016 13:20:39 GMT

A solution would be using AGGR as designed, with a inner aggregation function.

Please describe what vStartDate should return

Re: sum based on two dates

Wed, 20 Jul 2016 13:22:58 GMT

vStartDate should return the date_of_product which differs for each product

Re: sum based on two dates

Clever_Anjos — Wed, 20 Jul 2016 13:25:25 GMT

But what if you have "two products at the same time"? Which date should be returned?

Re: sum based on two dates

Clever_Anjos — Wed, 20 Jul 2016 13:26:58 GMT

Remember that a variable (like a function) in general returns an atomic value (only one)

Re: sum based on two dates

Wed, 20 Jul 2016 13:27:23 GMT

yes.. But this is what the requirement. So I couldn't find the solution. seeking the help of experts. Is there any way to achieve this ?

Re: sum based on two dates

Clever_Anjos — Wed, 20 Jul 2016 13:34:01 GMT

But what if you have "two products at the same time"? Which date should be returned?

Re: sum based on two dates

jyothish8807 — Wed, 20 Jul 2016 13:37:46 GMT

Hi Vinu,

Your variable will not return the correct value:

Try like this:

vStartDate = Date(aggr(min(Date_of_Product),product_key)

vEndDate=Addmonths(vstartDate,2)

exp:

= sum({<Date={">=$(vStartDate) <= $(vEndDate)"}>} Actuals)

Regards