Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Display message per selection

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.

161 Replies
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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))

Not applicable
Author

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'