Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
khanashique
Creator II
Creator II

Average of Months Value

Hi Experts,

I need help in calculating average of months value, i have a column which has value and other is month.

Here if we take example for Shoe average comes to 3754121, how can i get this figure by using some expression in Qlik Sense.

   

ItemValueMonth
Shoe24179571
Shoe56764372
Shoe4843263
Shoe75748094
Shoe26170745
Shirt21061731
Shirt37208182
Shirt43414183
Shirt34031924
Shirt18801575
Shirt24179576
Trouser56764371
Trouser4843262
Trouser75748093
Trouser26170744
Trouser21061735
Trouser37208186
Trouser43414187
Cap34031921
Cap18801572

Thanks,

MAK.

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Try this?

=Num(Avg({<Item={'Shoe'}, DateField = {">=$(=AddMonths(Max(DateField),-3))<=$(=Max(DateField))"}>} Value),'#,##0')

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

6 Replies
Anil_Babu_Samineni

How is your table looks like?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
khanashique
Creator II
Creator II
Author

Hi Anil,

I have change the question now...

Thanks.

Anil_Babu_Samineni

You can simplify this in straight table

Num(Avg(Value),'#,##0')


If this is in Text box

=Num(Avg({<Item={'Shoe'}>} Value),'#,##0')


Or Script

Load Item, Num(Avg(Value),'#,##0') as Value Resident Table Group By Item;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
khanashique
Creator II
Creator II
Author

Hi,

Thanks,

I need to take particular months average. There are total 7 months, and i need to take average for 3 or 4 months and has to be dynamic not static. for eg. we are in Nov month and need average for past 3 month (aug, sep, oct) and if we are in dec then we need average for (sep, oct, nov)...

How can we get this. please advise..

Thanks again.

MAK.

Anil_Babu_Samineni

Try this?

=Num(Avg({<Item={'Shoe'}, DateField = {">=$(=AddMonths(Max(DateField),-3))<=$(=Max(DateField))"}>} Value),'#,##0')

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
khanashique
Creator II
Creator II
Author

Thanks a lot, it worked...