Extend summary

[millmaine]

I have a layout summarising advertising activity and ticket sales for multiple events. 

I have tried a number of things.

First, in order to show dates (over a defined date range) that activity and sales occur I use a calculation field called "zs_GanttChart_rt":

Let ([Current_Date = Extend ( zv_StartDate_gd ) + Get (CalculationRepetitionNumber)]; 

Case ( IsEmpty ( Refiner_1A ) and Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_Start) +2; Extend (Z_colour); Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_End) +2; Extend (Z_colour) ))

That works fine.

Then I try to put sales figures in. First I simply used a repeating Summary Field with:

List of zs_GanttChart_rt

That works to a point. I get all the sales figures listed in the appropriate repeating field. However I don't actually want a list of each sale that occurs on a particular day. I want the total sales on that day.

So I created a calculation field on the sales items: GetSummary (Sum_Quantity; Date_Start)

Then tried this:

Let ([Current_Date = Extend ( zv_StartDate_gd ) + Get (CalculationRepetitionNumber)]; 

Case ( Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_Start) +2; Extend (Summary_Sales); Current_Date > Extend (Date_Start) and Current_Date < Extend (Date_End) +2; Extend (Summary_Sales) ))

Now here's where it gets interesting. If placed within sub summary part, it returns the correct value for the first relevant repetition only (and no other values). If placed in a grand summary part, it returns the correct value for the last relevant repetition only (and no other values).

I'm puzzled.

[comment]

This is rather difficult to follow - mainly because you described what you tried, but not what you want to accomplish.

 

[millmaine]

Fair enough.

Let's say we have an artist performing shows in 3 cities. On any given day there may be advertising activity for one or more of these shows.This activity may utilise a number of different media channels. We can display a summary of when this activity occurs by date and channel using repeating fields.

Each day there are certain number ticket sales for each of the three events We can display a summary of when sales have occurred by date using repeating fields.

What I'd like to do is show the total ticket sales each day.

At the moment the closest I can get is show a list of each city's sales on a particular date. What I want is the total sales each day.

 

[comment]

I am afraid I still don't see the entire picture. If you have a repeating calculation field that puts the amount of tickets sold in the repetition corresponding to a certain date, then a summary field defined as total of the repeating calculation field, summarize repetitions individually, will show the subtotals/totals for the dates when placed in a sub-summary/summary part. 

[millmaine]

My problem is defining "a repeating calculation field that puts the amount of tickets sold in the repetition corresponding to a certain date."

As I said in first post: if placed in a sub Summary it shows only first value; If placed in Grand Summary it shows only last value.

Edited July 4, 2019 by millmaine

[comment]

Well, given these fields describing an individual sale:

• TxDate
• TxQuantity

and assuming you want your columns to represent consecutive dates starting from the date held in a global gReportStartDate field, I would start be defining a repeating calculation cReportDatesR field (result is Date) =

Extend ( gReportStartDate ) + Get (CalculationRepetitionNumber) - 1

This can be used in the header part to show column labels. Next, define another repeating calculation field (result is Number) =

If ( Extend ( TxDate ) = cReportDatesR ; Extend ( TxQuantity ) )

This is the field you want to summarize.

 

If you do not need column labels, you can consolidate the calculation to =

If ( Extend ( TxDate ) = Extend ( gReportStartDate ) + Get (CalculationRepetitionNumber) - 1 ; Extend ( TxQuantity ) )

 

[millmaine]

That gives me the same result: if placed in a Sub Summary it shows only first value; If placed in Grand Summary it shows only last value. They appear on appropriate dates (as they did before) but I'm not getting all values.

Edited July 4, 2019 by millmaine

[comment]

The field you should be placing in the sub-summary and grand summary parts is the summary field. Based on your description, you have used the calculation field instead.

[millmaine]

Got it. Thanks...as always.