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]

Thanks Digvijay,

I will try it now and get back to you if I faced any difficulties.

Thanks.

[#3]

Hi Digvijay

I install Stunnel and each time I want to sent an email, I get the message below

[#4]

Hi Digvijay,

I install stunnel and each time I try to send an email, I get the message below

[Digvijay_Singh]

Did u receive any mail from google alerting for insecure access of your

credential? As I remember it didnt allow first time but when I followed

google mail and provided access, it started working.

Hope you can see stunnel config window in task bar showing successfull

installation, also chk if everytime new rows are added in that window when

you try to access.

[#6]

Hi,

Now I read the mail an allow access for less secure apps. Now it works.

Thanks Digvijay

[#7]

Hi Digvijay,

Please I am trying to generate an alert message that I want to send as email. From the table below all the locations with 0 Eventstanden values , i don't want them to be displayed in the message, only locations(EANCodes) with both Eventstanden and S.J.V should be displayed. But I don't know how to do that.

The expression for generating the Eventstanden column is :

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

and here is the entire expressions I used to display the message as shown in the image below.

='> 10% elektra data not received for ' &Street1&' '&Number&' ' &',Year 2015, months of -

'& chr(10)&

'Jaar'&Repeat(' ',10)&'EANCode'&Repeat(' ',10)&'Eventstanden'&Repeat(' ',10)&'S. J. V'&Repeat(' ',15)&'%' &chr(10)&

concat(aggr(if((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

-

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))/

(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

+ sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))*100> 0.10*100,

Only(Year) & ':' & '      '&Only(EANCode) & ':'&

  right('          ' & num(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2), '#,##0'),10) &

  '      ' & num((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)), '#,##0')&

  '      '&num((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

-

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))/

(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

+ sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)),'##0%')

  ,null()), Year,EANCode), '

', Month * +1)

Thanks

[Digvijay_Singh]

See if this can work -

='> 10% elektra data not received for ' &Street1&' '&Number&' ' &',Year 2015, months of -

'& chr(10)&

'Jaar'&Repeat(' ',10)&'EANCode'&Repeat(' ',10)&'Eventstanden'&Repeat(' ',10)&'S. J. V'&Repeat(' ',15)&'%' &chr(10)&

//new lines

if (sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)>0,

concat(aggr(if((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

-

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))/

(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

+ sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))*100> 0.10*100,

Only(Year) & ':' & '      '&Only(EANCode) & ':'&

  right('          ' & num(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2), '#,##0'),10) &

  '      ' & num((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)), '#,##0')&

  '      '&num((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

-

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))/

(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

+ sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)),'##0%')

  ,null()), Year,EANCode), '

', Month * +1)

//end of newly added condition

)

[Digvijay_Singh]

I am glad it worked finally.

You may mark responses like/helpful if you find replies helpful.

Take care,

DJ

[#10]

I tried and it did not work with the new expression you added. It stayed thesame.

[Digvijay_Singh]

Try this, I assume the expression for Eventstanden is resulting in whole 0 without any decimal else the new condition may not work. See if the end bracket is at the right place. I think this should work as we are only showing data rows for which expression value is greater than 0.

='> 10% elektra data not received for ' &Street1&' '&Number&' ' &',Year 2015, months of -

'& chr(10)&

'Jaar'&Repeat(' ',10)&'EANCode'&Repeat(' ',10)&'Eventstanden'&Repeat(' ',10)&'S. J. V'&Repeat(' ',15)&'%' &chr(10)&

concat(aggr(if((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

-

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))/

(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

+ sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))*100> 0.10*100,

//new lines

if (sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)>0,

Only(Year) & ':' & '      '&Only(EANCode) & ':'&

  right('          ' & num(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2), '#,##0'),10) &

  '      ' & num((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)), '#,##0')&

  '      '&num((sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

-

sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2))/

(sum({<Year = {2015},DataTypeID = {45},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)

+ sum({<Year = {2015},DataTypeID = {5},EnergyTypeID = {53},QualityScore = {">0"}>}Volume1 + Volume2)),'##0%')

  ,null())

//end of newly added condition

)

     , Year,EANCode), '

', Month * +1)