topic Re: Calculate sum amount based on Date conditions in QlikView

Calculate sum amount based on Date conditions

ahmadov_10 — Tue, 12 Mar 2019 10:35:38 GMT

Hi Experts,

i tried to work to calculate the sum(amount_pg) between the two dates in calendar choices (with Date range picker) . but when i modify the date filters , any modification for the the sum amount is maked to satisfy the conditions.

Any help please for a nice practice , method , extension to calculate sum based on dates condition ( i tried many but don't find the solution yet)

Re: Calculate sum amount based on Date conditions

Shubham_Deshmukh — Tue, 12 Mar 2019 11:02:06 GMT

Have you inserted if condition properly?

=Sum(if(date>=calendarObject1 and date <=calendarObject1,amount_pg))

Re: Calculate sum amount based on Date conditions

ahmadov_10 — Tue, 12 Mar 2019 11:24:21 GMT

=Sum(if(date>=calendarObject1 and date <=calendarObject1,amount_pg))

date (is my date of course) but what do you mean by calendarObject1 : is undifined for Qlik sense and i should correct my syntax. So , the problem how to set the two dates in choice into 2 variables ? this is what we need : sur a simple configuration we must do it ? any help

Re: Calculate sum amount based on Date conditions

vitaliichupryna — Tue, 12 Mar 2019 11:24:44 GMT

Hi,
When you use Date range picker you use one Data field: for example Date.
In this case for correct calculation you should have min and max date from picker, in your example is:
min = 01/01/2018 and max 31/07/2018
For this you should create two variables MinDate: Min(Date) and MaxDate: Max(Date)
After this you can use variables in your set expressions:
sum({<Date = {">=$(MinDate)<=$(MaxDate)"}>}amount_pg)
Thanks,
Vitalii

Re: Calculate sum amount based on Date conditions

Shubham_Deshmukh — Tue, 12 Mar 2019 11:36:08 GMT

I meant startDate and endDate which you used for calendar,
=Sum(if(date>=vStartDate and date <=vEndDate,amount_pg))
//date - your database date
//vStartDate and vEndDate are variables which you have used in calendars

As Vitalii suggested, you can also use set analysis for that,
=sum({<Date = {">=$(vStartDate )<=$(vEndDate)"}>}amount_pg)

Re: Calculate sum amount based on Date conditions

ahmadov_10 — Tue, 12 Mar 2019 12:00:27 GMT

Thanks for your answer but I still having problem : i make this configuration to the date range picker and my text with measure . DATFCH : date orders / MNTFCH : amount orders

But i still having the same amount 😞

Re: Calculate sum amount based on Date conditions

ahmadov_10 — Tue, 12 Mar 2019 15:11:11 GMT

to understand me more : i need to calculate my sum(MNTFCH) dynamically , based on the dates in filter from the date range picker (or other extension object , the essential : finding solution)

Re: Calculate sum amount based on Date conditions

vitaliichupryna — Wed, 13 Mar 2019 09:10:28 GMT

You can use my solution