topic Using Set Analysis to Get Avg in QlikView

Using Set Analysis to Get Avg

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have built a bar graph which gives me the Usage per country per month.

Most of the times, the usage is average for each month, but if for some reason there is a spike(20% of Average in any month) in usage for a country for a particular month compared to other months, only then the graph should display those countries. How can i achieve this using SET ANALYSIS?

Ex:

In the below graph that i have, data is displayed for last 4 months and the values are average for one country. As its average usage for all the months, this country should not be displayed.

But for another country, there was a major spike in one month, so this should be displayed in the Bar.

How can i achieve this?

I am looking only for those countries which have sudden peaks in usage compared to previous months. Something like if the usage is greater than 30% of the average for a particular month, only then display the graph.

The bars here represent month which are loaded as per below in script editor:

TO_CHAR(TO_DATE(EVT_DT, 'MM-DD-YYYY'),'MON-YYYY') as MONTH_YEAR,

**Values are displayed as 'AUG-2018','SEP-2018'

COUNTRY and MONTH_YEAR as used as Dimensions in Bar Chart

Value is used as an expression in bar chart with below:

Sum(AMOUNT)/1000000

Can someone please give me the Set analysis expression which can be used based on above?

Thanks

Taher

Re: Using Set Analysis to Get Avg

MarcoWedel — Tue, 18 Sep 2018 19:47:23 GMT

please post some sample data and your expected result

Re: Using Set Analysis to Get Avg

Anonymous — Tue, 18 Sep 2018 20:41:51 GMT

Hi Macro,

This is sample for one country(Where cost is pretty much average for last 4 months):

Here, average cost of Jamaica is 39700 which is within 30% range of all the months, so should not appear on Bar Chart

For another country, there is a spike(Not an average value between the months)

Here, average is 11000 and there is more than 30% difference between different months, so this needs to be displayed in Bar Chart.

So, i want to only display those countries that have more than 30% spike in any of the months.

Re: Using Set Analysis to Get Avg

Anil_Babu_Samineni — Wed, 19 Sep 2018 02:33:33 GMT

Would you able to share sample application rather images for us to make better clear. For now, If i understood correctly this is what you are requesting. Try this? And Suppress the null from dimension.

Dimension:-

If(Aggr(Sum(AMOUNT)/1000000, country)>=0.30, country)

Expression:-

Sum(AMOUNT)/1000000