Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
Can you share some sample to look into?
Hi,
Yes, a very small sample attached.
Best,
Simon
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.
Please share sample data
Attached is a small sample
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]))
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.
Thanks - that worked! I also expanded it to weeks
no bracket issues