Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with balances for every day of the month for 2 years (2018 to 2020)
two fields: Balance_date, Balance_amount
I want a chart that will only show me the monthends for these values.
Dimension would be: Year(Balance_date)&'-'&Month(Balance_date)
Expression: sum(Balance_amount)
I tried sum({< Balance_date = {'$(=Date(Monthend(Balance_date )))'}>} Balance_amount)
But it obviously didn't work...
Hi @sibrulotte
You can create one flag in script and use in the set analysis which is very flexible.
Load Balance_date, Balance_amount,
Dual(Year(Balance_date)&'-'&Month(Balance_date), MonthName(Balance_date)) as MonthYear,
If(Floor(Balance_date) = Floor(MonthEnd(Balance_date)), 1, 0) as Flag
from yoursourcetable;
In front end,
MonthYear as Dimension
and sum({<Flag={1}>}Balance_amount)
Hi @sibrulotte
You can create one flag in script and use in the set analysis which is very flexible.
Load Balance_date, Balance_amount,
Dual(Year(Balance_date)&'-'&Month(Balance_date), MonthName(Balance_date)) as MonthYear,
If(Floor(Balance_date) = Floor(MonthEnd(Balance_date)), 1, 0) as Flag
from yoursourcetable;
In front end,
MonthYear as Dimension
and sum({<Flag={1}>}Balance_amount)
Can you try this?
Sum({$<Balance_date ={'$(=MonthEnd(AddMonths(Balance_date), -1))'}>} Balance_amount)
or
if(monthend(Balance_date) = Balance_date, 1,0) as MonthEnd_Flag. -- script
Sum({$<MonthEnd_Flag = {"1"}>} Balance_amount)
Try this expression.
=sum({<Date = {"=Date=Dayname(Monthend(Date))"}>}Balance_amount)
See attached qvw
@sibrulotte You have received three good responses on this one, we would appreciate it if you would return to the thread and use the Accept as Solution on the post(s) that helped, or if you have further questions, please leave an update.
Regards,
Brett