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]

Yes I do and this is the expression i used

Count(DISTINCT{<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB','DM-E&A'},InvoiceTypeID = {2}>}PaidWithTax)

[Digvijay_Singh]

Can you tell me the hierarchy between Month, Shop, Product. As we need to use Aggr function to aggregate this count from top dimension to lower level dimension. See below one if it works - 

Try below expression,using your new expression -

=Sum(if(Aggr(Count(DISTINCT{<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB','DM-E&A'},InvoiceTypeID = {2}>}PaidWithTax),Shop,Start_Month,Product))>1,1)

I am using your count expression, aggregating it by Shop,Month and Product, if the count is greater than 1 then that is counted in the final sum.

Let me know the result, now my sample is not matching to test the above expression.

[#4]

Just use your sample to get the count of PaidWithTax. I was just using that as an example

[#5]

The hierarchy is the month, product then shop. The expression you suggested did not work. Maybe try using your sample and see what happens

[Digvijay_Singh]

Try this -

=Sum(if(Aggr(Count(DISTINCT{<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB','DM-E&A'},InvoiceTypeID = {2}>}PaidWithTax),Start_Month,Product,Shop)>1,1))

[Digvijay_Singh]

Updated sample, see the textbox showing 1.

[#8]

Its still showing that there are more than one PaidWithTax for a particular month with one each PaidWithTax for two different products per month

[Digvijay_Singh]

Try this -

=Count(if(Aggr(Count(DISTINCT{<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB','DM-E&A'},InvoiceTypeID = {2}>}PaidWithTax),Start_Month,Product,Shop)>1))

[#10]

Please I want these displayed as a message saying " Jan, Mar, Apr have PaidWithTaxt greater than one". So, I am trying the expression you sent me in a text box but its not working

[Digvijay_Singh]

Try this, try to adjust based on attached sample -

=Concat(if(Aggr(Count({<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB','DM-E&A'},InvoiceTypeID = {2}>}PaidWithTax),Start_Month,Product,Shop)>1,Start_Month),',') & ' have PaidWithTax greater than one'