Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
abhirup99
Contributor III
Contributor III

Find the sum of latest Cumulate_User for each account in a Monthly Trend line chart

AccountRef_dateCumulate_User
Private1-Jun-1830
Private5-Jun-1828
Private3-Jul-1836
Private10-Jul-1843
Public2-Jul-1820
Public20-Jul-1821
Public27-Jul-1825
Public5-Aug-1821
Public15-Aug-1823
Dual3-Apr-1812
Dual20-Apr-1815
Dual29-May-1820
Dual11-Jun-1818
Dual19-Jun-1819
 
 

I want to show this in a Monthly Trend line chart where my dimension is "Time_period" which has Month-Year like format Jun-18, May-18 and so on. For reference the Date field to create the dimension "Time_period" is "Ref_date".

So for a particular Month-Year the measure value should include sum of latest "Cumulate_User" for each account against that Month-Year. For example, In Jun-18 the measure value point should be 47 (=28+19), against Jul-18 the measure value point should be 61 (=36+25)and so on for rest of the rest of the Month-Year values. After when a person selects a particular Account from the Filter pane , the value of the measure should shrink down to the latest "Cumulate_User" value against that corresponding account for the particular Month-Year i.e. after the chart is built when I select Account='Private' my Monthly Trend Line chart for Jun-18 should have measure values shrink-ed from 47 to 28 and for Jul-18 and for rest.

Can you please help how to write down the correct Measure expression for the Monthly Trend Line chart as the below expression is not showing the right value when "Time_period" comes into play ??

sum(aggr(FirstSortedValue (Cumulate_User,-Ref_date),Account))

1 Solution

Accepted Solutions
OmarBenSalem

try : 

sum(aggr( FirstSortedValue (Cumulate_User,-Ref_Date),Time_Period,Account))

 

Result:Capture.PNG

If I select account = Dual:Capture.PNG

 

If I select Jun 2018 and Jul 2018:

Capture.PNG

 

View solution in original post

1 Reply
OmarBenSalem

try : 

sum(aggr( FirstSortedValue (Cumulate_User,-Ref_Date),Time_Period,Account))

 

Result:Capture.PNG

If I select account = Dual:Capture.PNG

 

If I select Jun 2018 and Jul 2018:

Capture.PNG