topic Re: Sum last value set expression included in App Development

Sum last value set expression included

blunckc1 — Sat, 16 Nov 2024 00:30:24 GMT

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

Re: Sum last value set expression included

Ksrinivasan — Tue, 02 Mar 2021 08:34:33 GMT

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

Re: Sum last value set expression included

blunckc1 — Tue, 02 Mar 2021 23:22:55 GMT

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?

Re: Sum last value set expression included

ArnadoSandoval — Wed, 03 Mar 2021 01:15:23 GMT

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.

Re: Sum last value set expression included

blunckc1 — Wed, 03 Mar 2021 01:55:42 GMT

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

Re: Sum last value set expression included

ArnadoSandoval — Wed, 03 Mar 2021 03:32:42 GMT

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,

Re: Sum last value set expression included

ArnadoSandoval — Wed, 03 Mar 2021 03:35:40 GMT

Hi @blunckc1

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

Re: Sum last value set expression included

blunckc1 — Wed, 03 Mar 2021 04:15:20 GMT

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

Re: Sum last value set expression included

Ksrinivasan — Wed, 03 Mar 2021 05:28:12 GMT

hi,

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

ksrininvasan

Re: Sum last value set expression included

Ksrinivasan — Wed, 03 Mar 2021 05:41:19 GMT

hi,

ksrinivasan

Re: Sum last value set expression included

ArnadoSandoval — Wed, 03 Mar 2021 05:44:51 GMT

Hi @blunckc1

Your [Start Date] column is coming from the PayPeriods table which 'associates' with your Contracts table on the SnapshotDate, eventually this association is part of the problem, suggestions:

Try using a Mapping Load to bring the 'Start Date' to your Contracts table level, with this script:

Map_PayPeriods: Mapping Load ContractsFull.SnapshotDate, "[Start Date]" FROM [lib://Sample_Data/SampleData-FirstSortedValue.xlsx] (ooxml, embedded labels, table is Sheet1); Contracts: LOAD ContractsFull.ContractID, ContractsFull.SnapshotDate, ApplyMap('Map_PayPeriods', ContractsFull.SnapshotDate) As [Start Date], ContractsFull.ContractedFTE, ContractsFull.ContractPeriod FROM [lib://Sample_Data/SampleData-FirstSortedValue.xlsx] (ooxml, embedded labels, table is Sheet1);

Attach your real application, so I could explore your tables association.

The ApplyMap should work.

hth

Re: Sum last value set expression included

blunckc1 — Wed, 03 Mar 2021 06:00:45 GMT

Hi @ArnadoSandoval

Yeah change at the data model layer is where I need to go...

Thanks heaps for your help!!

Cheers

Carl