Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Gauge to display Last month data

Dear Community:

Current month is October, but my Search data is one month lapse, which mean i have search data up to August only.

May I know how to display Max(Month) which is August data?

Current expression with error:

=sum({<[Date]={"$(=Date(AddMonths((Date),-1),'DD/MM/YYYY'))"},Company={'ABC'},Year=,Quarter=,Month=,Week=>} [Search Volume])

/sum({<[Date]={"$(=Date(AddMonths((Date),-1),'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>} [Search Volume])

Previously i use variable as varMonthStart =MonthStart(Max(Date)) with expression as shown below:

=sum({<[Date]={"$(=varMonthStart)"},Company={'ABC'},Year=,Quarter=,Month=,Week=>} [Search Volume])

/sum({<[Date]={"$(=varMonthStart)"},Year=,Quarter=,Month=,Week=>} [Search Volume])

But this will show September data which the data is not available yet...

Anyone can help to twist the expression?

Thanks and best regards,

Chanel

1 Solution

Accepted Solutions
Not applicable

Re: Gauge to display Last month data

Please find the below MTD Exp:

Previous MTD

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month-1))>$(=max({<Year={$(=max(Year))}>} Month)-2)"}>} Amount)))

Current MTD

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))>$(=max({<Year={$(=max(Year))}>} Month)-1)"}>} Amount))

4 Replies
Not applicable

Re: Gauge to display Last month data

Please find the below MTD Exp:

Previous MTD

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month-1))>$(=max({<Year={$(=max(Year))}>} Month)-2)"}>} Amount)))

Current MTD

(sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))>$(=max({<Year={$(=max(Year))}>} Month)-1)"}>} Amount))

senpradip007
Valued Contributor III

Re: Gauge to display Last month data

Hi,

You check the date formate.

Not applicable

Re: Gauge to display Last month data

Hi,

Set variable for max month with max date of August.

Not applicable

Re: Gauge to display Last month data

Awesome!!!! It's working! Thanks very muchie!!!

Community Browser