Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to sum value based on if statement i.e. if month is less than August then sum other wise "0". Following is what i am trying:
I have column of financial year (2017-2018, 2018-2019 etc.) and Month (01/04/2018,01/05/2018 et.) in my data along with Sale (Qty. in MT) column. Following is abstract of my data:
Group | Month | Financial Year | Sale |
Production_Sales qty. | 01-04-2018 | 2018-2019 | 1000 |
Production_Sales qty. | 01-05-2018 | 2018-2019 | 1000 |
Production_Sales qty. | 01-06-2018 | 2018-2019 | 1000 |
Production_Sales qty. | 01-07-2018 | 2018-2019 | 1000 |
Production_Sales qty. | 01-08-2018 | 2018-2019 | 1000 |
Production_Sales qty. | 01-09-2018 | 2018-2019 | 1000 |
Production_Sales qty. | 01-10-2018 | 2018-2019 | 1000 |
I wanted to sum all qty. of months which are less than 01/08/2018. following is my formula:
if(Group='Production_Sales qty.',if(Month <= '01/08/2018',sum(Sale),0))
I have list box of Month, so when i select any month less than August 18 it gives answer for that particular month only (i.e. 1000) but when no selection is done or month is selected which is greater than Aug 18 then it does not show anything.
What i want is when no selection is done or month less than August 18 or even greater month selection is done from Aug 18 then also it should show 5000 MT Qty. only.
Please help me out in this.
Thanks & Regards,
Ashish
try with below set expression,
=sum({<Group={'Production_Sales qty.'},Month={"<=01-08-2018"}>}Sale)
Regards,
Thank you for the reply!!!!!!
It does give a correct answer. Wanted to know that can i use if statement in Set expression, because i have two more column which is plant & product. There are 5 plants & 9 products and the <=01-08-2018 condition is for one plant and one product only. {$< Plant = 'NDL', Product = 'OPC-Self'>} and there are other condition also for different product & Plant that's why i wanted if statement instead of set expression. Therefore i need if statement which ignore list box selections for Months.............
Thanks & regards,
Ashish
I tried your formula but when i enter your formula it gives correct answer for whole year but when i select month from the list box it does not take the selection in consideration it keep on giving whole value for the year as a whole and not for that particular month..........
Please help me out, where i am going wrong......
Thanks & Regards,
Ashish