topic Only show monthends in a graph in QlikView

Only show monthends in a graph

sibrulotte — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Only show monthends in a graph

MayilVahanan — Thu, 29 Oct 2020 03:04:12 GMT

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)

Re: Only show monthends in a graph

Chanty4u — Thu, 29 Oct 2020 04:27:40 GMT

Can you try this?

Sum({$<Balance_date ={'$(=MonthEnd(AddMonths(Balance_date), -1))'}>} Balance_amount)

if(monthend(Balance_date) = Balance_date, 1,0) as MonthEnd_Flag. -- script

Sum({$<MonthEnd_Flag = {"1"}>} Balance_amount)

Re: Only show monthends in a graph

Vegar — Thu, 29 Oct 2020 21:20:49 GMT

Try this expression.

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

See attached qvw

Re: Only show monthends in a graph

Brett_Bleess — Thu, 05 Nov 2020 22:24:01 GMT

@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