Only show monthends in a graph

[sibrulotte]

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...

[MayilVahanan · Accepted solution]

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)

[Chanty4u]

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)

[Vegar]

Try this expression.

=sum({<Date = {"=Date=Dayname(Monthend(Date))"}>}Balance_amount)

See attached qvw

[Brett_Bleess]

@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