Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
blunckc1
Creator
Creator

Sum last value set expression included

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

Labels (3)
11 Replies
Ksrinivasan
Specialist
Specialist

hi,

DIM: Start date

Expression of bar chart:

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

your bar chart will be show below result in chart.

Ksrinivasan_0-1614674012223.png

ksrini

 

blunckc1
Creator
Creator
Author

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:

blunckc1_1-1614727364426.png

 

Any ideas?

ArnadoSandoval
Specialist II
Specialist II

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.

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
blunckc1
Creator
Creator
Author

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
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
ArnadoSandoval
Specialist II
Specialist II

Hi @blunckc1 

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

Results-01.png

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
blunckc1
Creator
Creator
Author

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

blunckc1_1-1614744865416.png

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
Specialist
Specialist

hi,

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

Ksrinivasan_1-1614749155338.png

 

Ksrinivasan_0-1614749105848.png

ksrininvasan

 

Ksrinivasan
Specialist
Specialist

hi,

Ksrinivasan_0-1614750026159.png

 

ksrinivasan