Drilldown time dimension: get the last date in each period

[#1]

Hi,

I am currently building a bar chart using a drilldown dimension that goes from YearMonth to YearWeek to Date (I have created a master calendar). The time granularity of my data is daily, and I have a field called 'value' that I want to sum.

I want the graph to show the sum of 'value' per the latest date in every period. I.e. when looking at YearMonth I want each bar to be the sum of 'value' per the last date of each month; when drilling down to YearWeek I want each bar to be the sum of 'value' per the last date of each week; and when looking at dates I want each bar to be the sum of 'value' for that date.

Is this possible to do in QlikView? I have started testing a couple of my ideas using set analysis, but haven't got the desired result, so now I'm turning to all of you 😃

Thanks in advance!

Kind regards

Henrik

[swuehl · Accepted solution]

Maybe like this, using an expression

=FirstSortedValue( aggr(sum(value), Date), aggr( -Date, Date) )

[#3]

Thanks swuehl, it seems to work!

[marakud971]

hi !

how to use this expression : =FirstSortedValue( aggr(sum(value), Date), aggr( -Date, Date) )

to calculate the same for last year ?

I would like to calculate a delta with this expression between current year and last year but i've to compare last date of each périod.

Thanks