topic Re: Drilldown time dimension: get the last date in each period in QlikView

Drilldown time dimension: get the last date in each period

Mon, 25 Nov 2013 14:01:03 GMT

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

Re: Drilldown time dimension: get the last date in each period

swuehl — Mon, 25 Nov 2013 14:19:18 GMT

Maybe like this, using an expression

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

Re: Drilldown time dimension: get the last date in each period

Mon, 25 Nov 2013 14:31:03 GMT

Thanks swuehl, it seems to work!

Re: Drilldown time dimension: get the last date in each period

marakud971 — Thu, 18 Jun 2020 12:53:10 GMT

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