Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Digvijay,
Please see the attached excel files.
Thanks
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.
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
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))
Here is the sample - I considered >5% as irregularity, you may change it as per the need.
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.
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 -
Download version 5.35 installer.exe
Regarding alert, set analysis can be adjusted to include multiple location, days, weeks etc.
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 -
Download version 5.35 installer.exe
Regarding alert, set analysis can be adjusted to include multiple location, days, weeks etc.
my posts are going to admin for approval, not sure when they will be cleared.
No problem whenever its cleared I will get them and get back to you if I have any further questions,
Thanks