Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using Set Analysis to Get Avg

Hi

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.

Pic1.PNG

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

Capture1.PNG

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

3 Replies
MarcoWedel

please post some sample data and your expected result

Anonymous
Not applicable
Author

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

Capture_JM.PNG

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.

Capture_Spike.PNG

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

Anil_Babu_Samineni

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

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)