Hi,
I want to create a calculated field where if the order_day is in between 1st day to 10th day of a month then multiply 1.5 * sales,
if the order_day is between 11-23 then multiply 2* sales ,
else 4*sales.
i have already written a calculated field below i want to include the above logic too in the same formula can someone help me with this ?
ifelse(month - {latest month numbner} = 0 AND {order_day} AND year =2023, {sales}, 0))
i want to multiply a constant value to a column if date is between 17-18 of any month in quicksight
@Aditya_Raj
If order_day gives you the 1 - 31 value, then you can just nest the ifelse. The example below takes sales2 if order day between 1-10, takes sales4 if order day between 11 and 18, all other days sales*8.
ifelse(month - {latest month numbner} = 0 AND {order_day}>=1 AND {order_day}<=10 AND year =2023, {sales}*2,
month - {latest month numbner} = 0 AND {order_day}>=11 AND {order_day}<=18 AND year =2023, {sales}*4,
{sales}*8)
it is giving me the below error.
Expression ifelse({order_day} < 5 ,{gms},0) for function < has incorrect argument type Date < Number. Function syntax expects ‘<COMPARABLE_TYPE> < <COMPARABLE_TYPE>’.
@Aditya_Raj -
This error appears to be saying that order_day is a date field. You can’t compare a date field to a numeric. Create a new field using extract(‘DD’,{order_day}). This will give you a numeric value of the day value of your date field. Use this new field in your calculation. Or you may be able to do it right within the same calc like: ifelse(extract(‘DD’,{order_day}) < 5 ,{gms},0)