Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I have a table with date and segment wise balances.
Now I want to get total of all balances for the segment's last balance at any month closing.
For Example:
Date Segment Balance
01-JAN-2017 A 10
02-JAN-2017 A 11
10-JAN-2017 A 12
31-JAN-2017 A 13
15-JAN-2017 B 20
31-JAN-2017 B 25
Now I want to get JAN Balances
A= 13 (As it is last closing balance)
B = 25 (As it is last closing balance)
For total Jan Balance It should be 13=25=38
Regards,
Zahid Rahim
Like This:
jan 2017 38
feb2017 48
mar2017 58
Sum(Aggr(FirstSortedValue(Balance,-Date),Segment,Month))
Hi Zahid,
the above mentioned solution is ready to be used when you have "real" joined table (not associated) in Qlik. Could you please provide your script to find solid solution for you?
Thank you.
BR
Martin
What is the difference between real and associated join?
Hi,
You can try below mentioned,
Now I want to get JAN Balances
A= 13 (As it is last closing balance)
B = 25 (As it is last closing balance),
Ans : Sum(Aggr(FirstSortedValue(Balance,-Date),Segment))
For total Jan Balance It should be 13=25=38,
Ans : Sum(Aggr(RangeSum(Above(Sum(Balance),0,RowNo(Total))),Segment,Date))