Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
richard_pearce6
Luminary Alumni
Luminary Alumni

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
swuehl
MVP
MVP

Richard,

try

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

View solution in original post

5 Replies
swuehl
MVP
MVP

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

richard_pearce6
Luminary Alumni
Luminary Alumni
Author

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

richard_pearce6
Luminary Alumni
Luminary Alumni
Author

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]

;

swuehl
MVP
MVP

Richard,

try

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

richard_pearce6
Luminary Alumni
Luminary Alumni
Author

Perfect!! Worked a treat!! Thanks Stefan