Sum last value set expression included

[blunckc1]

Hi community,

Need some help with an expression for a bar chart.  Attached is a spreadsheet with sample data for you to refer to.

Essentially, for every [Start Date] I need to calcuate:

Sum({$<[ContractsFull.ContractPeriod]={'Future'}>} [ContractsFull.ContractedFTE])

But as you can see there are multiple results per [Start Date].  So I only want the calculation to include data in the row of latest ContractsFull.SnapshotDate per [Start Date].

I tried using FirstSortedValue within an Aggr() expression but can't get it to work properly... Any ideas?

Note: [Start Date] actually comes from another table of data that joins by the ContractsFull.SnapshotDate, like a master calendar.

Thanks

Carl

[Ksrinivasan]

hi,

DIM: Start date

Expression of bar chart:

(Sum({$<ContractsFull.ContractPeriod = {"Future"}>} ContractsFull.ContractedFTE))

your bar chart will be show below result in chart.

ksrini

[blunckc1]

Hi Ksrinivasan,

Thanks but see how it is suming the values for all SnapshotDates that relate to that start date?

I need it to just sum the last value from within each Start Date range.  Can't use distinct because in the full data set, there are 1,000s of employees included.  The ContractID column identifies an individual, so I need to include something like FirstSortedValue and grouping it by the ContractID...

So it should come out as:

Any ideas?

[ArnadoSandoval]

Hi @blunckc1 

You should attach a better data sample, the current one only contains the ContractID 0017626332012900, then We don't have enough data to help you.

[blunckc1]

Hi @ArnadoSandoval,

More data attached 🙂 the pivot table shows the expected result in the bar chart.

I know I can achieve this by making changes to how I load the data, but want to see if that can be avoided...

Cheers

Carl

[ArnadoSandoval]

Hi @blunckc1 

Thanks for the new sample of data and the pivot table you included, it helped me to reconcile the results, first let me explain my steps (I attached the solution as well).

• After loading your original data sample, I created the sheet Back-Up, this is when I realized your original sample only contained a single ContradID, still with this data I created the two KPIs, for the SUM(ContractsFull.ContractedFTE) with a couple of conditions on its SET analysis expression.
• With the second data sample, I created the New Sheet and the Set expression below while filtering on the Start Date = 8/3/2021, the last column on the top table shows the expected result, although with some noise as the table contains too many columns.

Aggr(Sum({<"[Start Date]"={'8/3/2021'},ContractsFull.ContractPeriod={'Future'}>} [ContractsFull.ContractedFTE]), [ContractsFull.SnapshotDate])

• The second table (bottom of the sheet) shows three KPIs for the [Start Date] column, the first one is plain, just targeting the date '8/3/2021',  the second KPI no longer filter on a particular target date, although the [Start Date] field remains as part of the SET expression, then it Aggr the Sum() on the ContractID and SnapshotDate.

Aggr(Sum({<"[Start Date]"=,ContractsFull.ContractPeriod={'Future'}>} [ContractsFull.ContractedFTE]), [ContractsFull.SnapshotDate])

• The final KPI counts the number of ContractID producing the expected result.

Aggr(Count({<"[Start Date]"=,ContractsFull.ContractPeriod={'Future'}>} [ContractsFull.ContractID]), [ContractsFull.SnapshotDate])

• Finally, the Include zero values property, in the Data Handling group is unchecked.

Hope this helps,

[ArnadoSandoval]

Hi @blunckc1 

This is the screenshot showing the results from the attached application.

[blunckc1]

Hi @ArnadoSandoval ,

Thanks so much for this work. Weirdly, I get "-" when using this expression:

Aggr(Sum({<"[Start Date]"=,ContractsFull.ContractPeriod={'Future'}>} [ContractsFull.ContractedFTE]), [ContractsFull.SnapshotDate])

The data model is slightly different though, will this cause it?

If you change your load script to this your data model will match mine (basically).

Contracts:
LOAD
ContractsFull.ContractID,
ContractsFull.SnapshotDate,
ContractsFull.ContractedFTE,
ContractsFull.ContractPeriod
FROM [lib://Sample_Data/SampleData-FirstSortedValue.xlsx]
(ooxml, embedded labels, table is Sheet1);

PayPeriods:
Load
ContractsFull.SnapshotDate,
"[Start Date]"
FROM [lib://Sample_Data/SampleData-FirstSortedValue.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Ksrinivasan]

hi,

only condition you have to select Future from filter or create bookmark as Default "Future"

ksrininvasan

[Ksrinivasan]

hi,

ksrinivasan