Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

erickd1190
New Contributor III

Help with Chart expression!

HI Experts!

I am having a couple issues.

The first is the Sum of my daily target and then multiply by the number of working days within date range.

I get a number using:

Sum(DailyPT)*Networkdays(Min(date),Max(date)))

However what I really need to use is:

SUm(DailyPT)*Networkdays(Monthstart(date),Monthend(date)))

because it should include all work days in each month and if we are only halfway through a month, still count the days to come.

This expression doesn't work, i get a null value.

DailyPT is a dollar target like $395.

Monthly should be: 395 * (# of m-f days in month)

Yearly should be: 395 * (# of work days in a year)

Any thoughts?

8 Replies
MVP
MVP

Re: Help with Chart expression!

May be like this?

SUm(DailyPT)* Sum( Aggr(Networkdays(Monthstart(Max(date)),Monthend(Max(date))),MonthField))

erickd1190
New Contributor III

Re: Help with Chart expression!

Hi!

Thank you! This solved the expression issue.

Unfortunately, this doesn't do what I had hoped for the application.

MVP
MVP

Re: Help with Chart expression!

I could perhaps help you better if you could share your sample qvw explaining your expected output.

brunobertels
Valued Contributor

Re: Help with Chart expression!

Hi

May be this

SUm(DailyPT)*Networkdays(Monthstart(min(date)),Monthend(max(date))))

vikraant
Contributor

Re: Help with Chart expression!

I am trying this out with set expression. Please check if this works !

sum( {<WeekDayField={'>=1<=5'}, date = {"<=$(=MonthStart(Max(date)))>=$(=MonthEnd(Max(date)))"} >}DailyPT)

erickd1190
New Contributor III

Re: Help with Chart expression!

I have attached some dummy data for you to test with.

brunobertels
Valued Contributor

Re: Help with Chart expression!

hi

chek this app

for desired result :

Total Sales = Sum(rate)

Target = SUm(DailyPT)*Networkdays(Monthstart(min(Date)),Monthend(max(Date)))

Variance =

Sum(rate)

/

(

SUm(DailyPT)*Networkdays(Monthstart(min(Date)),Monthend(max(Date)))

)

erickd1190
New Contributor III

Re: Help with Chart expression!

Hi Bruno,

In my simplified example this does work.

Why then in my actuals this isn't correct?

Could it be my autogenerate calendar mixing with my fiscal calendar?