Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
please post some sample data and your expected result
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.
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