Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Create an alert based on multiple condition

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.

1 Solution

Accepted Solutions
Highlighted

Re: Create an alert based on multiple condition

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;

Capture.PNG

View solution in original post

8 Replies
Highlighted
Not applicable

Re: Create an alert based on multiple condition

Anyone any thoughts on this?

Highlighted
Not applicable

Re: Create an alert based on multiple condition

swuehl

stalwar1

Please help me get a solution to this post.

Highlighted

Re: Create an alert based on multiple condition

Try attached

Highlighted
Not applicable

Re: Create an alert based on multiple condition

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?

Highlighted
Not applicable

Re: Create an alert based on multiple condition

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.

Highlighted

Re: Create an alert based on multiple condition

Checking

Highlighted

Re: Create an alert based on multiple condition

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;

Capture.PNG

View solution in original post

Highlighted
Not applicable

Re: Create an alert based on multiple condition

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