Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Luminary
Luminary

Calculate yearly and quarterly values based on most recent monthly totals

Hi,

My data is monthly and quarterly and yearly stats would be the latest monthly total (not summed up months). For example, if my data were:

Load * Inline [
Month,Qtr,Year,Amount
1,1,2012,10
2,1,2012,10
3,1,2012,10
4,2,2012,20
5,2,2012,20
6,2,2012,20]
;

Monthly Results would be as above 10,10,10,20,20,20

Quarter 1 Results would be 10

Quarter 2 Results would be 20

Yearly Results would be 20

I could script this into a data table but I thought there would be an elegant solution. My final design would have a chart with a cycle group allowing the user to change between; Month, Qtr and Year. I've tried a few idea's using different functions but nothing seems to get the results I'm after.

Can anyone assist please?

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculate yearly and quarterly values based on most recent monthly totals

Richard,

try

=FirstSortedValue(aggr(sum(Amount),Month),-aggr(Month,Month))

View solution in original post

5 Replies
MVP
MVP

Re: Calculate yearly and quarterly values based on most recent monthly totals

You can probably use

=FirstSortedValue(Amount, -Month)

to return the amount for the latest month, depending on your group by context, you should get what you want.

Hope this helps,

Stefan

Luminary
Luminary

Re: Calculate yearly and quarterly values based on most recent monthly totals

Morning Stefan, thanks for your response.

I did try using FirstSortedValue and even tried using it with an aggr function with no luck. Unfortunately your example below (directly copied in) gives a null value, could you give me a bit more context as perhaps there's something further I need to do to get it working?

Many thanks

Richard

Luminary
Luminary

Re: Calculate yearly and quarterly values based on most recent monthly totals

Hi again,

Sorry my last post wasn't right. Your expression did work on the dataset and gave the correct results. I over simplyfied the question for the purpose of here. The actual monthly data is sumed up from multiple lines; for example:

 

Load

* Inline [
Qtr,Month,Year,Amount
1,1,2012,1
1,1,2012,2
1,2,2012,10
1,2,2012,20
1,3,2012,100
1,3,2012,200
2,4,2012,300
2,4,2012,400
2,5,2012,500
2,5,2012,600
2,6,2012,700
2,6,2012,800]

;

MVP
MVP

Re: Calculate yearly and quarterly values based on most recent monthly totals

Richard,

try

=FirstSortedValue(aggr(sum(Amount),Month),-aggr(Month,Month))

View solution in original post

Luminary
Luminary

Re: Calculate yearly and quarterly values based on most recent monthly totals

Perfect!! Worked a treat!! Thanks Stefan