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]

So please once I create the master calendar what will be the expression to display the message per customer selection?

[Digvijay_Singh]

Once you have master calendar, your month dimension now must show zero sales values.

Please ensure date field of your transaction table is connected properly with this master calendar.

I don't think any measure change in the expression but it may need small formatting etc kind of minor changes.

You can scramble the data and share the  sample. Re: What is scrambling & Why to Use it....

[#4]

Hey Digvijay,

Please  I am facing some problems customizing the Master Calendar script. So from the data model the only date related field is the StartDate in the Invoice table. Could you please help me to construct the Master calendar from these please? Thanks.

[Digvijay_Singh]

Use below script  in the new tab at the end, Ensure StartDate field is a date -

//Create StartDate Calendar

MinMax_StartDate:

Load

  Min(StartDate) as MinDate_StartDate,

  Max(StartDate) as MaxDate_StartDate

Resident Invoice;

Let vMinDate_StartDate = Peek('MinDate_StartDate',0,'MinMax_StartDate');

Let vMaxDate_StartDate = Peek('MaxDate_StartDate',0,'MinMax_StartDate');

Let vDiff = vMaxDate_StartDate - vMinDate_StartDate + 1;

StartCalendar:

Load *,

  Date(MonthStart(StartDate),'YYYY-MM') as Start_YearMonth,

  Start_Year & '-' & Start_Quarter As Start_YearQuarter,

  Start_WeekYear & '-' & Num(Start_Week,'00') as Start_YearWeek;

Load

  Date(StartDate,'YYYY-MM-DD') as StartDate,

  Year(StartDate) as Start_Year,

  Month(StartDate) as Start_Month,

  Day(StartDate) as Start_Day,

  Week(StartDate) as Start_Week,

  'Q' & ceil(Month(StartDate)/3) as Start_Quarter,

  WeekYear(StartDate) as Start_WeekYear;

Load

  RecNo()-1+$(vMinDate_StartDate) as StartDate

AutoGenerate($(vDiff));

Drop table MinMax_StartDate;

[Digvijay_Singh]

Change format in below line as per your need -

Date(StartDate,'YYYY-MM-DD') as StartDate,

[#7]

Thanks alot,

Please could you also help me with the expression on how to display the message for months that are missing payments?

[Digvijay_Singh]

Its difficult to ensure 100% correctness without trying on real sample, try this - changes sales and Shop fields to real names.

='There were no sales in the months of ' & concat({<Start_Month = {"=Sum(Sales)=0"}>}Start_Month,',')&' '&'for '&GetFieldSelections(Shop)

[Digvijay_Singh]

I removed sorting attribute as of now, will see on that later, first you try to make the above expression work.

[#10]

I tried and it did not work

Here is the expression I use to get the sales in the actual app:

sum(DISTINCT{<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB'},InvoiceTypeID = {2}>}PaidWithTax). But I don't know to fit that in in place of the Sales field in your expression.

[Digvijay_Singh]

Try like this -

='There were no sales in the months of ' & concat({<Start_Month = {"=sum(DISTINCT{<Abonnement = {'DM-CFV','DM-CFB','DM-E&F','CFB'},InvoiceTypeID = {2}>}PaidWithTax)=0"}>}Start_Month,',')&' '&'for '&GetFieldSelections(Shop)

So the logic is we are finding and later concating all months where the sum expression results is 0.

The single quote used in element list(e.g. 'DM-CFV') may create formatting issue but need to try and check