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.

[#2]

Hi Digvijay,

Please see the attached excel files.

Thanks

[Digvijay_Singh]

How you are getting value more than 2000, as per ACTUAL formula and the data shared if you add Piek and Dal, its not more than 1500, I don't see irregular values of both in the data, in fact above and below you can find similar data values. May be I am missing something.

Just out of curosity, whats the english meaning of Piek and Dal. Thanks.

[#4]

Piek and Dal means high and low. So during high tarief period for example € 0.05 cent per Kilowaat of electricity/hour ,the piek was the consumption and when prices are low say € 0.02 cent per Kilowaat of electricity/hour, Dal was the consumption.

So first I used the Begin column to create a calendar as follows:

Calendar:

LOAD DISTINCT

  Date(Begin,'DD-MM-YY') as Begin,

  Year(Begin) as Start_Year,

  Month(Begin) as Start_Month,

  Date(Monthstart(Begin), 'MMM -YYYY') as YearMonth,

  'Q' & Ceil(Month(Begin)/3) as Quarterz,

  Dual(Year(Begin) & '-Q' & Ceil(Month(Begin)/3), Year(Begin) & Ceil(Month(Begin)/3)) as YearQtr,

  Week(Begin) as WeekNo,

  AutoNumber(Week(Begin),Year(Begin)) as Week2,

  WeekDay(Begin) as Week,

  Hour(Begin) as Uur

  RESIDENT DeMaas;

Then I used Uur as the Dimension later used these expression to get the Actual per hour for the day say Monday:

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

You see the values are for every 15mins of an hour.

01-01-16 0:00

01-01-16 0:15

01-01-16 0:30

01-01-16 0:45

01-01-16 1:00

So what I am trying to achieve is each time there is an irregular pattern per location as you see for example on Monday at 2pm then I should generate an alert.

Thanks

[Digvijay_Singh]

I tried to create the sample for 1st part, I still didn't understand G.V. and WMV part.

I tried to do it through scripting as alert formula could be very complex otherwise.

Script used -

Transaction:

LOAD Location,

     Date(Floor(Begin),'DD-MM-YY') as Begin,

     Timestamp(Begin,'DD-MM-YYYY hh:mm:ss') as [Begin Time],

     Timestamp(Eind,'DD-MM-YYYY hh:mm:ss') as [End Time],

     Hour(Begin) as Hour,

     LocatieType,

     Piek,

     Dal,

     T,

     Gas,

     LocationID

FROM

[..\7 - Sample Data\XLSnXLSX\Bloemolen.xlsx]

(ooxml, embedded labels, table is [BK Bloemmolen 87168791000034098]);

LOAD Location,

     Date(Floor(Begin),'DD-MM-YY') as Begin,

     Timestamp(Begin,'DD-MM-YYYY hh:mm:ss') as [Begin Time],

     Timestamp(Eind,'DD-MM-YYYY hh:mm:ss') as [End Time],

     Hour(Begin) as Hour,

     LocatieType,

     Piek,

     Dal,

     Gas,

     T,

     LocationID

FROM

[..\7 - Sample Data\XLSnXLSX\De Maas.xlsx]

(ooxml, embedded labels, table is Blad1);

Volume:

LOAD //Location,

     LocationID,

     Date(PeriodStart,'DD-MM-YY') as PeriodStart,

     Volume1,

     Volume2,

     DataTypeID

FROM

[..\7 - Sample Data\XLSnXLSX\G.V.xlsx]

(ooxml, embedded labels, table is Blad1);

Calendar:

LOAD DISTINCT

  Date(Floor(Date#(Begin,'DD-MM-YY')),'DD-MM-YY') as Begin,

  Year(Begin) as Start_Year,

  Month(Begin) as Start_Month,

  Date(Monthstart(Begin), 'MMM -YYYY') as YearMonth,

  'Q' & Ceil(Month(Begin)/3) as Quarterz,

  Dual(Year(Begin) & '-Q' & Ceil(Month(Begin)/3), Year(Begin) & Ceil(Month(Begin)/3)) as YearQtr,

  Week(Begin) as WeekNo,

  AutoNumber(Week(Begin),Year(Begin)) as Week2,

  WeekDay(Begin) as Week

  RESIDENT Transaction;

  Left Join(Transaction)

    Load *,

  Num(Previous(HourTotal)/HourTotal,'#0.0%') as %Change;

  Load LocationID,

  Begin,

  Hour,

    Sum(Piek+Dal) as HourTotal

  Resident Transaction

  Group By LocationID,Begin,Hour;

   Exit script;

Alert condition -

Min({<LocationID={867},WeekNo={10},Start_Month={'Mar'},Week={'Tue'}>}%Change) <=0.95 or Max({<LocationID={867},WeekNo={10},Start_Month={'Mar'},Week={'Tue'}>}%Change) >=1.05

Alert Message -

=if(Min({<LocationID={867},WeekNo={10},Start_Month={'Mar'},Week={'Tue'}>}%Change) <=0.95 or

  Max({<LocationID={867},WeekNo={10},Start_Month={'Mar'},Week={'Tue'}>}%Change) >=1.05,

  'Location'&Repeat(' ',10)&'WeekNo'&Repeat(' ',10)&'Day'&Repeat(' ',10)&'Hour'&chr(10))

  &

Concat(distinct {<LocationID={867},WeekNo={10},Start_Month={'Mar'},Week={'Tue'},%Change={"<=.95>=1.05"}>}

  Location&Repeat(' ',10)&WeekNo&Repeat(' ',10)&Week&Repeat(' ',10)&Hour,chr(10))

[Digvijay_Singh]

Here is the sample - I considered >5% as irregularity, you may change it as per the need.

[#7]

Hi Digvijay,

Thanks alot for the sample I will try to apply it now and get back to you if I have any further question. So for the Alert Condition and Alert Message I can just add all the different LocationID's and if the condition is met I will see all the different locations and hours in the message right?

For G.V and WMV what I mean there is for example I have the table below

LocationID        G.V       WMV

867                   3,808     3,589

876                   3,500     2,985

So what I want to achieve here also is if the difference between G.V and WMV is greater than 10%, then I should create an alert as well.

Have you also try to figure out the the email thing yet?

Once again thanks for all your help I really do appreciate it alot.

[Digvijay_Singh]

Regarding email - I just tried Stunnel as suggested here by Bill -

GMail setting not worked in Mail server Qlikview 11

It worked properly, I just installed it and made changes in user preferences as pr below - also first time google blocked it but sent me mail to open the less secure channel of mail, I opened the link and selected 'on' and next time when I opened the doc I received the mail in gmail -

Stunnel installer site is -

stunnel: Downloads

Download version 5.35 installer.exe

Regarding alert, set analysis can be adjusted to include multiple location, days, weeks etc.

[Digvijay_Singh]

my last post went into moderation, sending again, this may go through

Regarding email - I just tried Stunnel as suggested here by Bill -

GMail setting not worked in Mail server Qlikview 11

It worked properly, I just installed it and made changes in user preferences as pr below - also first time google blocked it but sent me mail to open the less secure channel of mail, I opened the link and selected 'on' and next time when I opened the doc I received the mail in gmail -

Stunnel installer site is -

stunnel: Downloads

Download version 5.35 installer.exe

Regarding alert, set analysis can be adjusted to include multiple location, days, weeks etc.

[Digvijay_Singh]

my posts are going to admin for approval, not sure when they will be cleared.

[#11]

No problem whenever its cleared I will get them and get back to you if I have any further questions,

Thanks