I have below requirement:
Have to display all flag values in current year(based on logic) and Sales amount for the current month amount. I have these data items ready though how to formulate the logic to display amount in current month.Have tried with DATE=?selected date? Then amount else FLAG, this is returning case statement data type error and not working. Please help with this.
Thank you
Quote from: cielo on 31 Oct 2019 01:16:00 PM
I have below requirement:
Have to display all flag values in current year(based on logic) and Sales amount for the current month amount. I have these data items ready though how to formulate the logic to display amount in current month.Have tried with DATE=?selected date? Then amount else FLAG, this is returning case statement data type error and not working. Please help with this.
Thank you
Hi,
I'm struggling to make sense of what you've written here. What constitutes a flag value? Is it based on a value in an item - if so, what value and what item?
A couple of pointers to get you started.
For anything within the current year, you'll need to compare the year part of your date item with the year part of current_date in a filter, eg
extract(year, [your Date item]) = extract(year, current_date)
If within this you want sales amounts for the current month, you could use a calculation with the expression
if (extract(month, [your Date item]) = extract(month, current_date)) then ([your Sales Amount item]) else (0)
Cheers!
MF.
Sorry for the insufficient details,
I have flag value based another field value, it will contain 'Yes/No' values, requirements is to display Flag values in all the 12 months though Sales amount in current month.I need to write a CASE logic to display whether FLAG/Sales amount value to show.I have separate Outer query for Flag values (filter for whole year) and current month values will come different query.
Please help
Thank you!
Quote from: cielo on 31 Oct 2019 02:45:20 PM
Sorry for the insufficient details,
I have flag value based another field value, it will contain 'Yes/No' values, requirements is to display Flag values in all the 12 months though Sales amount in current month.I need to write a CASE logic to display whether FLAG/Sales amount value to show.I have separate Outer query for Flag values (filter for whole year) and current month values will come different query.
Please help
Thank you!
Hi,
I'm still confused. In the first post you mentioned you wanted to see values in the current year (ie that would be 2019), but in the latest post you mention wanting to see values for 12 months (ie November 2018 to October 2019). Can you clarify?
Whichever you go for, the second expression I included in the last post should give you the sales amount for just the current month (ie October 2019). Have you tried it to see if it delivers what you need?
MF.
I would like to see all the values in current year, for 2019 Jan-Sep should display Flag values and October should display Amount,for next Month Nov should display Amount and till October Flag values.I am facing some difficulty to write case statement to include Amount and Flag values in the statement.
case
[Date] between _make_timestamp(year(current_date),1,1) and _last_of_month(_add_months(current_date),-1) then [Flag]
else [Amount]
end
That's just off the top of my head, so please check for typoes etc.