topic Re: Create an alert based on multiple condition in QlikView

Create an alert based on multiple condition

Thu, 02 Feb 2017 17:58:10 GMT

Hi Experts,

Based on the attached data model I need to create an alert if it meets the following condition.

Condition 1: ActualUptimePercent < CommittedUptimePercent for a given month and

Condition 2: if ActualUptimePercent>CommittedUptimePercent for next three consecutive months.

If you see in the attached datamodel file first 4 line items meet condition 1 but don't meet condition 2 so how do I only pull the ones in green where it meets both conditions. If you see the line Item in green for Datamonth 7/1/2016 and 1/1/2016 it meets both the above conditions.

I have attached the dashboard where I can make the first condition work but I am need to incorporate both the conditions to pull only the line items highlighted in green in the data model.

Re: Create an alert based on multiple condition

Fri, 03 Feb 2017 14:56:35 GMT

Anyone any thoughts on this?

Re: Create an alert based on multiple condition

Fri, 03 Feb 2017 15:12:38 GMT

swuehl‌

stalwar1‌

Please help me get a solution to this post.

Re: Create an alert based on multiple condition

sunny_talwar — Fri, 03 Feb 2017 15:14:15 GMT

Try attached

Re: Create an alert based on multiple condition

Mon, 06 Feb 2017 19:02:03 GMT

Sunny, thank you for your response. This works fine with the Excel Data Model that I sent but when I apply the same logic to my actual data model it's pulling a lot of line items where the given condition is not met and in some cases it's missing the ones that should be pulled. For example in the attached model it's pulling the purple ones but missing the green one which should be reported. This is happening for rest of the data in the datamodel. Any thoughts?

Re: Create an alert based on multiple condition

Mon, 06 Feb 2017 21:59:32 GMT

stalwar1‌

Please find attached a larger version of the data model where the suggested logic is not working 100%. If you see the Servername 'AAA' the dashboard is showing the yellow highlighted line items which are not meeting the two conditions and it's not showing the green one where both conditions are met. Likewise same thing is happening for rest of the data where it's reporting corrected somewhere but not everywhere. To meet both conditions and to be shown on the dashboard first ActualUptimePercent should be less than CommittedUptimePercent for a given month and then for next three consecutive months ActualUptimePercent should be greater than or equal to CommittedUptimePercent.

I am attaching the datamodel and the dashboard for your reference. Please let me know if you have a solution.

Re: Create an alert based on multiple condition

sunny_talwar — Mon, 06 Feb 2017 22:01:54 GMT

Checking

Re: Create an alert based on multiple condition

sunny_talwar — Mon, 06 Feb 2017 22:07:24 GMT

Try this:

Table:

LOAD

DataMonth,

Date(MonthStart(DataMonth,0),'MM/DD/YYYY') as DataMonth2,

Server,

Environment,

CommittedUptimePercent,

ActualUptimePercent,

if(ActualUptimePercent<CommittedUptimePercent,1,0) as downtime,

Server & Environment as RewardID

FROM

DB.xlsx

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD*,

If(Server = Previous(Previous(Previous(Server))) and Environment = Previous(Previous(Previous(Environment))) and Previous(ActualUptimePercent) > Previous(CommittedUptimePercent)

and Previous(Previous(ActualUptimePercent)) > Previous(Previous(CommittedUptimePercent))

and Previous(Previous(Previous(ActualUptimePercent))) > Previous(Previous(Previous(CommittedUptimePercent))), 1, 0) as Flag2

Resident Table

Order By Server, Environment, DataMonth desc;

DROP Table Table;

Re: Create an alert based on multiple condition

Thu, 09 Feb 2017 20:03:01 GMT

This worked out perfectly fine. Thank you for your help Sunny. Really appreciated.