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,

Please I have tried the expression as you suggested but I am only getting the message below without the months:

"There were no sales in the months of for Hilltop58".

[Digvijay_Singh]

Are you able to see 0 sales against the missing month in the straight table now?

Does your Sum function individually gives result?

Make some selections here and there and see if you can figure out something.

[Digvijay_Singh]

Can you share data model with calendar table included, How many years of data you have, try selecting one year first in list box and check?

[#5]

Yeah If check the box "Show All values" in the Dim tab, I see 0 sales in the months. But when making a selection, those months with no sales don't appear in the message.

The thing is when no invoice is received for a particular month, that month is not entered to that customers information. Maybe that is the problem. Because even after creating the master calendar, it does not show missing sales month per selection.

[Digvijay_Singh]

I think it should work, you are very close to the final thing. Could be some minor issue in the final expression

We don't need fact records for missing months, since transaction date is connected to all the dates between start and end date of your transaction through master calendar, it should identify and tell us 0 values when we choose month as dimension.

Is this expression working in text box when you select individual month -

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

Have year and month as listboxes( you have these fields in your calendar) and try different selections.

Can you share latest data model? You had shared old one earler.

[#7]

Yes the expression works in text box. but if I select a month that no sales was originally registered, it just cancels the selection.

I just exported the datamodel image I don't know why its got duplicate of the same tables in the picture.

[Digvijay_Singh]

Hope you are using Start_Month as the dimension, and you are checking it in straight table.

It must cancel the selection when you choose month in a list box having no sales recorded because there is no registered sales for the selected shop but in straight table it should show 0 value for selected month. Hope 'you have unchecked suppress zero values in presentation tab -

If you want selection in months should not impact your straight table expression, you can ignore month selection by changing expression like -

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

[#9]

In the Straight table  I see all the months with 0 values. Just that in the message that needs to be displayed using a text box, the months don't appear. That's the problem right now.

[Digvijay_Singh]

I think I understood your problem, created attached sample, I hope it will resolve your problem.

the expression I used is -

='There were no sales in the months of ' & Concat(distinct {1<Start_Month=E({$<Start_Month={"=sum(DISTINCT PaidWithTax)<>0"}>}Start_Month)>}Start_Month,',',Num(Start_Month))

  &' '&'for '&GetFieldSelections(Shop)

Now keeping your data in mind, it should change to -

='There were no sales in the months of ' & Concat(distinct {1<Start_Month=E({$<Start_Month={"=sum(DISTINCT{$<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB'},InvoiceTypeID = {2}>})PaidWithTax)=0"}>}Start_Month)>}Start_Month,',',Num(Start_Month))

  &' '&'for '&GetFieldSelections(Shop)

Let me know how it goes

[#11]

Hi Digvijay,

It worked. Thank you so much. You have really been patient to help me out. I appreciate that thank you. But please I only want that message displayed if a particular shop meets that condition. Could you help me out again please?