Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
deblina_rai
New Contributor II

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

Tags (1)
17 Replies
MVP
MVP

Re: Last monthly amount for each quarter using set analysis

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
New Contributor II

Re: Last monthly amount for each quarter using set analysis

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 ?

Re: Last monthly amount for each quarter using set analysis

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
New Contributor II

Re: Last monthly amount for each quarter using set analysis

 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.

Re: Last monthly amount for each quarter using set analysis

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
New Contributor II

Re: Last monthly amount for each quarter using set analysis

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.

Re: Last monthly amount for each quarter using set analysis

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
New Contributor II

Re: Last monthly amount for each quarter using set analysis

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
New Contributor II

Re: Last monthly amount for each quarter using set analysis

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?