Display message per selection

[#1]

Hey guys,

From the table below the goal is to have sales every month for all the shops shown on the right. But if for example when selecting a shop in this case Hilltop 58 and there are months without any sales as in the case  of Mar, Jun, Oct and Dec, then a message should be displayed saying " There were no sales in the months of Mar, Jun, Oct and Dec for Hilltop 58".

Please any help on how to achieve these will be great.

Month              Sales

Jan                  € 2,500,25

Feb                  € 1,252,00

Mar

Apr                    € 500.25

May                  € 3,500,95

Jun

Jul                    € 4,652,85

Aug                    € 10,458,87                                          SHOPS

Sep                    € 15,896,45                                        Hilltop 58

Oct                                                                              T- Square 45

Nov                    € 25,875,65                                        Spring bell 38

Dec                                                                              Down beach 10

                                                                                    Coastal city 5

Thanks in advance.

[Digvijay_Singh]

You can use pick/match -

Pick(Match(Name,'Simon','Daniel'),0.5 * (sum(Sales),0.3 * (sum(Sales))

You can add as many names as you want but keep no of names and no of expression same so that you have expression for each name.

You can read more on pick/match in Qlikview help in case you want to know more on how it works.

Regards,

Digvijay

[#3]

Thanks alot. Please another question. On setting email alerts no one in the community has been able to give me a correct answer. If I go to User Preferences ->Mail and then under the server section I do the following:

Address : smtp.mail.yahoo.com

Port : 465

Authentication Method: AUTH LOGIN

User ID : my yahoo email address

Password : ********

After doing the above and when I click on the Test button I keep having these error message

Could not connect to server. Last Response. Unexpected SMTP Login response,Last Response:

My next question is I have Sales Reps with the following weekly targets and corresponding sales

Sales Rep                    Target                   Sales

Simon                           € 10,000                € 7,500

Martin                            € 15,000               € 17,000

Peter                              € 12,500               € 10,000

John                               € 7,500                € 3,500

Maurice                           € 10,500              € 9,500

Now I want to be able to create an alert message that when I reload the app, it should display Sales Reps who did NOT meet their target, what their targets were and how much they sold and the percentage of their sales with respect to their targets.

Thanks.

[Digvijay_Singh]

For email part I would suggest you to check with someone who can correct your SMTP settings in the machine.

For alert related point you may use sample as attached.

[#5]

Please could you send me the expression you use for displaying the Alert? I am opening it with a qlikview version from an OEM partner that does not even display the menu so I can't really go there to see the expression, that's why I just work with my Personal Edition all the time.

For the email part can't you direct me step by step on how to correct my SMTP settings on my machine?

Thanks very much for your help  Digvijay always. I really do appreciate it.

[Digvijay_Singh]

Here is the sample script used -

Load *,

  Num(Sales/Target,'#0.0%') as PercentSales,

  if (Sales-Target<0,1,0) as MissFlag

;

Load * inline [

Sales Rep,                    Target,                   Sales

Simon,                            10000 ,                7500

Martin ,                            15000  ,              17000

,

Peter  ,                             12500 ,               10000

John  ,                              7500  ,               3500

Maurice  ,                          10500  ,             9500 ];

Alert windows looks like below -

and message expression is -

='Target Missed By - '& chr(10)&

'Sales Rep'&Repeat(' ',5)&'Target'&Repeat(' ',14)&'Sales'&Repeat(' ',14)&'% Sales' &chr(10)&

concat({<MissFlag={1}>}[Sales Rep]&Repeat(' ',14)&Target&Repeat(' ',14)&Sales&Repeat(' ',14)& PercentSales,chr(10))

Let me try on email thing -

[#7]

Hi Digvijay,

Thank you very much! I will be waiting on the email part. If that can work out, I will be so happy.

[#8]

Hi Digvijay,

Please based  on the images below, the following is what I want to try and achieve. I am doing electricity analysis on a weekly basis with data collected from smart meters for different locations. So, for example in the first image, the location BM: De Maas 11, there is an irregularity in the pattern at 2 PM in the afternoon from Monday to Friday based on the expression Actual. So, I want to be able to create an alert message saying "There is an irregularity in the electricity consumption pattern for week 10 for the following locations". Then I want to be able to display the message as below;

Location              WeekNo        Day            Hour 

BM: De Maas        10                Monday      14

BM: De Mass        10                Tuesday      14

e.t.c

And for the other location BK: Bloe... you also see that on Wednesday there is also an irregularity. So thesame message

Location              WeekNo          Day                Hour

BK: Bloe...            10                  Wednesday      9 - 15    

Overall each time I reload the app, I will like to see the locations, WeekNo, Day and Hour having irregularities in the patterns.

In addition, I also want to display an alert message if the difference between the G.V and WMV is greater than 10%.

The expressions I used for calculating the  G.V, WMV and Actual are as follows:-

G.V: sum({<DataTypeID = {7},Month = {Mar},Year = {2016},Week = {Mon},WeekNo = {10}>}Volume1)

WMV : sum(wmvElek)

Actual : sum({<Month = {Mar},Year = {2016},Week = {Mon},WeekNo = {10}>}Piek + Dal)

Thanks. Also still waiting on the email part. Once again thank you very much for all your help.

[Digvijay_Singh]

Hi Simon,

Can you pl share sample data, also from ACTUAL expression I didn't get how to trigger irregularity. Please explain the condition. Please share data so that I can use the above expression to create the alert. I was busy in few things, will see on email thing asap. Take care,

Digvijay

[#10]

Hi Digvijay,

Below is the sample data for creating the Actual expression: So the condition for triggering the alert is: "Each day with and irregular pattern in the graph like you see at 2 pm for these location, an alert should be triggered " with the message like I explain before. Thank you.

So the values are quarter values. Each 15min the values are registered by the smart meters for Piek, Dal and Gas.

[Digvijay_Singh]

Cannot use the image in sample, can u share excel or txt file. Thanks