Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-10-12 07:06 AM

how to exclude year wise min and max values?

Hi

I have a scenario the date is like below

    

DateRegionLocationSales
2013/NovIndiaDelhi89
2013/DecIndiaDelhi96
2014/JanIndiaDelhi101
2014/FebIndiaDelhi99
2014/MarIndiaDelhi99
2014/AprIndiaDelhi102
2014/MayIndiaDelhi102
2014/JunIndiaDelhi115
2014/JulIndiaDelhi122
2014/AugIndiaDelhi136
2014/SepIndiaDelhi128
2014/OctIndiaDelhi143
2014/NovIndiaDelhi149
2014/DecIndiaDelhi158
2015/JanIndiaDelhi172
2015/FebIndiaDelhi185
2015/MarIndiaDelhi194
2015/AprIndiaDelhi263
2015/MayIndiaDelhi278
2015/JunIndiaDelhi333
2015/JulIndiaDelhi355
2015/AugIndiaDelhi389
2015/SepIndiaDelhi389
2015/OctIndiaDelhi415
2015/NovIndiaDelhi423
2015/DecIndiaDelhi445
2016/JanIndiaDelhi460
2016/FebIndiaDelhi460
2016/MarIndiaDelhi480
2016/AprIndiaDelhi499
2016/MayIndiaDelhi522
2016/JunIndiaDelhi522
2016/JulIndiaDelhi561
2016/AugIndiaDelhi588
2016/SepIndiaDelhi613

Here i need to calculate averages like

12 months means last 12 months average and in that we need to exclude max and min vlaues

24 months means   last 24 months average and here we need to exclude first 12 months max and min and above 12 months max and min  i.e. 2 max values and 2  min values


same for 36 3 max values and 3 min values(for every 12 months one max and min)



Please do the needful.



Thanks

Thiru

891 Views
0 Likes
6 Replies
sunny_talwar
MVP
MVP
‎2016-10-12 07:08 AM

Two questions

1) Is this needed in the script or front end?

2) Can you provide the exact output numbers under one of the three scenarios based on your input data above?

776 Views
0 Likes
Anil_Babu_Samineni
MVP
MVP
‎2016-10-12 07:20 AM

Do you mean values are Sales. And what was the expeted o/p

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
776 Views
0 Likes
Not applicable
‎2016-10-12 09:08 AM
Author

In response to sunny_talwar

Hi Sunny,

I need script and front end as well.

out put is

12 months

---------------

    

2015/AugIndiaDelhi3899.58
2015/SepIndiaDelhi3890.00
2015/OctIndiaDelhi4156.68
2015/NovIndiaDelhi4231.93
2015/DecIndiaDelhi4455.20
2016/JanIndiaDelhi4603.37
2016/FebIndiaDelhi4600.00
2016/MarIndiaDelhi4804.35
2016/AprIndiaDelhi4993.96
2016/MayIndiaDelhi5224.61
2016/JunIndiaDelhi5220.00
2016/JulIndiaDelhi5617.47
2016/AugIndiaDelhi5884.81
2016/SepIndiaDelhi6134.25

here  9.58 and 0 s we need to exclude

24 months

--------------

    

2014/AugIndiaDelhi13611.48
2014/SepIndiaDelhi128-5.88
2014/OctIndiaDelhi14311.72
2014/NovIndiaDelhi1494.20
2014/DecIndiaDelhi1586.04
2015/JanIndiaDelhi1728.86
2015/FebIndiaDelhi1857.56
2015/MarIndiaDelhi1944.86
2015/AprIndiaDelhi26335.57
2015/MayIndiaDelhi2785.70
2015/JunIndiaDelhi33319.78
2015/JulIndiaDelhi3556.61
2015/AugIndiaDelhi3899.58
2015/SepIndiaDelhi3890.00
2015/OctIndiaDelhi4156.68
2015/NovIndiaDelhi4231.93
2015/DecIndiaDelhi4455.20
2016/JanIndiaDelhi4603.37
2016/FebIndiaDelhi4600.00
2016/MarIndiaDelhi4804.35
2016/AprIndiaDelhi4993.96
2016/MayIndiaDelhi5224.61
2016/JunIndiaDelhi5220.00
2016/JulIndiaDelhi5617.47
2016/AugIndiaDelhi5884.81
2016/SepIndiaDelhi6134.25

here  9.58 and 0 s we need to exclude for 2016 to 2015

and 35.57 and -5.88 we need to exclude for 2015 to 2014



this values for calculating average we need to exclude and calculate average.


In front end we have to show the excluded values.


I am working on the qvd file,i will uplaod ASAP once it is done

Thanks

Thiru


776 Views
0 Likes
Not applicable
‎2016-10-12 09:37 AM
Author

In response to Anil_Babu_Samineni

Hi Anil,

Yes Normally average means we will take 12 values,but here we need to exclude max and min vlaues per year while calculating the average.

Thiru

776 Views
0 Likes
Anil_Babu_Samineni
MVP
MVP
‎2016-10-12 10:05 AM

In response to

Have you tried any expression, Or Else please share me application

Dimensions are -

Date, Region, Location, Sales

12 Months -- Avg({<Date = {"$(=AddMonths(Date,-12))"}, Sales -= {"$(=Max(Sum(Sales)))", "$(=Min(Sum(Sales)))"}>}Sales)

24 Months -- Avg({<Date = {"$(=AddMonths(Date,-24))"}, Sales -= {"$(=Max(Sum(Sales)))", "$(=Min(Sum(Sales)))"}>}Sales)

I am not sure, This way we can achieve. Would you able to provide sample

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
776 Views
0 Likes
Anonymous
Not applicable
‎2016-10-12 10:07 AM
Author

=avg({<sales = -{"=Max(sales)"}>}sales)..exclude maximum sales

=avg({<sales = -{"=Min(sales)"}>}sales).....exclude minimum sales

776 Views