Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
deblina_rai
Contributor III
Contributor III

Last monthly amount for each quarter using set analysis

Input DAta:

Product type

Asset Year

Asset  Quarter

Asset Month

Amount

MF

2019

Q1

JAn

39M

MF

2019

Q1

Feb

33M

MF

2019

Q1

Mar

40M

MF

2019

Q2

Apr

50M

MF

2019

Q2

MAy

67M

MF

2019

Q2

Jun

41M

 

Out put data: I need  latest month amount for each quarter while selecting two month like below

Product type

Asset Year

Asset  Quarter

Amount

MF

2019

Q1

40M

MF

2019

Q2

41M

Labels (2)
17 Replies
tresesco
MVP
MVP

Try using FirstSortedValue() , like:

=FirstSortedValue(Amount, -Month)                 //This would work if your month field is a dual field

 

Also, if you have multiple transactions against a single month, you have to use sum() along with aggr(), like:

=FirstSortedValue(Aggr(Sum(Amount), Month), -Month)

And if your month field is a text field, try like:

=FirstSortedValue(Aggr(Sum(Amount), Month), -Date#(Month, 'MMM'))

deblina_rai
Contributor III
Contributor III
Author

Its is working for one product but if i select two product type as dimension or if there is no filter then the chart giving wrong blank values for some product. 

 

Can you please give any other way ?

sunny_talwar

May be try this

=FirstSortedValue(
   Aggr(
     Sum(Amount)
   , [Product type], [Asset Year], [Asset Quarter], [Asset Month]),
  -Aggr(
     [Asset Month]
   , [Product type], [Asset Year], [Asset Quarter], [Asset Month])
)
deblina_rai
Contributor III
Contributor III
Author

 actually i need to make this measure as dynamic master measure so that it can work for any dimension. So we cant add Product type in aggr level. Product type   can be change to product name or something else.

sunny_talwar

How do you change the dimension in the chart? May be you can use the same logic to change the dimension in your expression

deblina_rai
Contributor III
Contributor III
Author

i am creating master measure for a self service app. So this measure will be used in any chart . So basically requirement is to make two measure one for latest month amount of each quarter & latest month amount of each year.
sunny_talwar

May be use GetObjectField to dynamically get the dimension from your object. This might not be perfect, but you can play around with the idea to make this work for you.

=FirstSortedValue(
   Aggr(
     Sum(Amount)
   , $(=GetObjectField(0)), $(=GetObjectField(1)), $(=GetObjectField(2)), [Asset Month]),
  -Aggr(
     [Asset Month]
   , $(=GetObjectField(0)), $(=GetObjectField(1)), $(=GetObjectField(2)), [Asset Month])
)

 

deblina_rai
Contributor III
Contributor III
Author

i got your point. But i try  this option , i didn't find any mistakes in the  syntax but its giving invalid.

IS there any other option with out using fisrtsortedvalue function?

 

May be my number of dimensions also vary .

deblina_rai
Contributor III
Contributor III
Author

No of dimension can vary  as its self service app. So is there any other way to achieve this  with out using first sorted value function?