topic Re: Sum(if(and formula in QlikView

Sum(if(and formula

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

This is driving me crazy. I'm trying to summarize all bookings that has the same month and year as the reload time with this formula;

Sum(if(year([Booking date])=year(ReloadTime()) and month([Booking date])=month(ReloadTime()),[Net Sales LC]))

However it returns 0.

Can anyone help me?

Best,

Simon

Re: Sum(if(and formula

vishsaggi — Fri, 02 Feb 2018 00:27:14 GMT

Can you share some sample to look into?

Re: Sum(if(and formula

Anonymous — Fri, 02 Feb 2018 03:20:56 GMT

Hi,

Yes, a very small sample attached.

Best,

Simon

Re: Sum(if(and formula

tresB — Fri, 02 Feb 2018 04:37:06 GMT

At first look, I see bracket issues:

Sum(if(year([Booking date])=year(ReloadTime()) and month([Booking date])=month(ReloadTime()),[Net Sales LC]))

If this doesn't help, try to share more information about your issue and share a sample/screen shot may be.

Re: Sum(if(and formula

shiveshsingh — Fri, 02 Feb 2018 05:42:02 GMT

Please share sample data

Re: Sum(if(and formula

Anonymous — Fri, 02 Feb 2018 13:45:50 GMT

Attached is a small sample

Re: Sum(if(and formula

vishsaggi — Fri, 02 Feb 2018 17:10:32 GMT

Can you try this and let me know what you getting?.

SumSales:

LOAD *, Year([Booking date]) AS BookingYear, Month([Booking date]) AS BookingMonth,

Year(ReloadTime()) AS ReloadYr, Month(ReloadTime()) AS ReloadMonth;

LOAD [Booking date],

[Sold Units],

[Net Sales LC]

FROM

[..\Downloads\Sample.xlsx]

(ooxml, embedded labels, table is page);

Change the excel path accordingly and run. By the way your data for booking date has January and when you do reloadtime which is going to be feb (which is current month and year) you will get all 0's. So i changed the Booking date values to Feb and ran the expression.

This is what i see when i run the expression.

= Sum(if(BookingYear = ReloadYr AND BookingMonth = ReloadMonth,[Net Sales LC]))

Re: Sum(if(and formula

tamilarasu — Fri, 02 Feb 2018 19:30:14 GMT

Hi Simon,

Your expression looks good to me. There are 2 possibilities.

1) [Booking date] field might be loaded as text format in Qlikview. So the year and month function returns nothing and your expression returns 0. So check the format of the [Booking Date] field and change it to date format (If it is not loaded as date format).

2) If you reload your application on Feb month and if there are no sales for the month of Feb then the net sales will be 0. So check whether is there any sales for the month of Feb or not.

Good luck.

Re: Sum(if(and formula

Anonymous — Mon, 05 Feb 2018 14:21:08 GMT

Thanks - that worked! I also expanded it to weeks

Re: Sum(if(and formula

larara — Fri, 22 Nov 2019 02:56:19 GMT

no bracket issues