Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jhona1737
New Contributor II

MTD SalesGoals

Hi Everyone, I would like to ask what formula should I used if you have the sales goals for the entire month to show me the sales goals MTD,

This is the one I am using right now but gave me the total sales goals for the month

sum(if (InMonth ([InvoiceDate], SetDateYear (Today(), max (total Year ([InvoiceDate]))), 0), [SalesGoals]))


Thanks for your help!

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: MTD SalesGoals

You might need to create the master calendar, then add MTD flag

If(InMonthToDate(SalesDate,Today(),0,7) * -1=1,1,0) As MTDFlag

You might need to check the InMonthToDate function to understand how it works. In my example, Financial year start Jul so i put 7 there.

Then in your expression, you can just use like Uday mentioned Sum({$<MTDFlag={1}>} [SalesGoals])

4 Replies
isaaclin
New Contributor III

Re: MTD SalesGoals

Try:

sum({$<[InvoiceDate]={">=$(=Num(MonthStart(Max([InvoiceDate]))))<=$(=Max([InvoiceDate]))"}>}[SalesGoals])

uday_pasupuleti
Contributor II

Re: MTD SalesGoals

HI Jonathan,

Try the following in your master calendar

If(Day(your date field) <= Day(Today()),1,0) as MTD

then in your expression use

Sum({$<MTD={1}>} [SalesGoals])

Thanks,

Uday

Not applicable

Re: MTD SalesGoals

You might need to create the master calendar, then add MTD flag

If(InMonthToDate(SalesDate,Today(),0,7) * -1=1,1,0) As MTDFlag

You might need to check the InMonthToDate function to understand how it works. In my example, Financial year start Jul so i put 7 there.

Then in your expression, you can just use like Uday mentioned Sum({$<MTDFlag={1}>} [SalesGoals])

jhona1737
New Contributor II

Re: MTD SalesGoals

Thank you, Nhu Ngo

It works!!

I appreciate

Community Browser