Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Item | Value | Month |
Shoe | 2417957 | 1 |
Shoe | 5676437 | 2 |
Shoe | 484326 | 3 |
Shoe | 7574809 | 4 |
Shoe | 2617074 | 5 |
Shirt | 2106173 | 1 |
Shirt | 3720818 | 2 |
Shirt | 4341418 | 3 |
Shirt | 3403192 | 4 |
Shirt | 1880157 | 5 |
Shirt | 2417957 | 6 |
Trouser | 5676437 | 1 |
Trouser | 484326 | 2 |
Trouser | 7574809 | 3 |
Trouser | 2617074 | 4 |
Trouser | 2106173 | 5 |
Trouser | 3720818 | 6 |
Trouser | 4341418 | 7 |
Cap | 3403192 | 1 |
Cap | 1880157 | 2 |
Thanks,
MAK.
Try this?
=Num(Avg({<Item={'Shoe'}, DateField = {">=$(=AddMonths(Max(DateField),-3))<=$(=Max(DateField))"}>} Value),'#,##0')
How is your table looks like?
Hi Anil,
I have change the question now...
Thanks.
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;
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.
Try this?
=Num(Avg({<Item={'Shoe'}, DateField = {">=$(=AddMonths(Max(DateField),-3))<=$(=Max(DateField))"}>} Value),'#,##0')