topic Re: How to write an expression for below requirement in App Development

How to write an expression for below requirement

kunkumnaveen — Fri, 27 Dec 2019 12:05:27 GMT

Hello All,

I need to design a bar graph with Dimension (B_Mode)and with a expression which accumulate last three months avg(Count_Br ) excluding the selected month

For Example :i have max data till 2019 Oct.

if no month and year is selected it has to then take the avg of past three months(Sep,Avg,Jul)

if any month is selected (2019,Feb) then it should take avg of the past three months(2019Jan,2018Dec,2018,Nov)..

Chart:Bar Chart

Dimension: B_Mode

Expression: Avg({ ? }Count_Br)

Thank

Please find the attached sample data...

Re: How to write an expression for below requirement

Taoufiq_Zarra — Fri, 27 Dec 2019 13:57:55 GMT

"if any month is selected (2019,Feb) then it should take avg of the past three months(2019Jan,2018Dec,2018,Nov"

not clear ?

Re: How to write an expression for below requirement

kunkumnaveen — Fri, 27 Dec 2019 14:23:37 GMT

Hi ,

I mean if a year 2019 and month Feb is selected from year and month filters.

What is the AVG of past three months is:

AVG of past three months are values of

2019 Jan

2018 Dec

2018 Nov

should bypass the year selection

Re: How to write an expression for below requirement

Anil_Babu_Samineni — Fri, 27 Dec 2019 14:27:16 GMT

Perhaps this?

Avg({<Month={">=$(=AddYears(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 04:36:41 GMT

Hi Anil,

I have tried your expression, but it not working ..something is missing in below bold set analysis

Avg({<Month={">=$(=AddYears(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 08:39:03 GMT

Hello All,

Any suggestion for the above requirement ...

Thanks

Re: How to write an expression for below requirement

Anil_Babu_Samineni — Mon, 30 Dec 2019 08:41:48 GMT

My bad, Should be

Avg({<Month={">=$(=AddMonths~~AddYears~~(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 09:53:34 GMT

I have done a change still its not working

Avg({<Month={">=$(=Date(AddMonths(Max({<Year>}Month),-3),'MMM')<=$(=Max({<Year>}Month))"}>}sales)

Re: How to write an expression for below requirement

Taoufiq_Zarra — Mon, 30 Dec 2019 10:00:08 GMT

@Anil_Babu_Samineni's function works very well

=Avg({<Month={">=$(=AddMonths(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} Sales)

why are you adding Date() ?

if not can you share your Qlik file ?

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 10:09:44 GMT

Hi,

Sorry to say but i am not allowed to share it,but i have attached a sample data file.

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 10:13:14 GMT

I have break the expression and took in two kpi,

Re: How to write an expression for below requirement

Taoufiq_Zarra — Mon, 30 Dec 2019 10:18:00 GMT

can you look at the QLik file attached

Avg in blue

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 10:44:07 GMT

Hi,could you plz post me that expression what you have used in text box ,as i am using qliksense ....

Re: How to write an expression for below requirement

Taoufiq_Zarra — Mon, 30 Dec 2019 10:45:22 GMT

=Avg({<Month={">=$(=AddMonths(Max({<Year>}Month),-3))<=$(=Max({<Year>}Month))"}>} [sum(Count_Br)])

Re: How to write an expression for below requirement

Kushal_Chawda — Mon, 30 Dec 2019 10:56:52 GMT

try below script

avg({<Year,M_Month, Date = {">=$(=monthstart(addmonths(max(Date),-3)))<=$(=monthend(addmonths(max(Date),-1)))"}>}Count_Br)

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 10:57:52 GMT

Hi,i think i didn't explained my requirement clear. In a chart i need to show avg of each B_mode (Avg means when no year or month is selected it has to take that particular B_mode past three months avg excluding max date)

i mean in below screen for B_mode =PER,the max date is 27/10/2019 so avg for Per is Avg (27/09/2019+27/08/2019+27/07/2019) which is 341

Your expression,what i am getting is entire yr avg when no year and month is selected

Re: How to write an expression for below requirement

Kushal_Chawda — Mon, 30 Dec 2019 10:58:50 GMT

Did you tried my solution?

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 11:07:39 GMT

Hi,Thanks for your reply ,Yes i tried below exp

avg({<Year,M_Month, [Date(Date)] ={">=$(=monthstart(addmonths(max([Date(Date)]),-3)))<=$(=monthend(addmonths(max([Date(Date)]),-1)))"}>}
[sum(Count_Br)])

But i am getting same output instead of Max three months(sep,Avg,Jul)

Re: How to write an expression for below requirement

kunkumnaveen — Mon, 30 Dec 2019 11:09:38 GMT

Please find attached sample data

Re: How to write an expression for below requirement

Kushal_Chawda — Mon, 30 Dec 2019 11:27:21 GMT

check my reply again. you are not using same expression,

don't use date(date) in set analysis, just use date field name

don't use sum(coumt_br), just use count_br