topic get values of weekend dates for several weeks in QlikView

get values of weekend dates for several weeks

felcar2013 — Fri, 02 Dec 2016 18:03:53 GMT

dear community.

i Need to Display in a bar Chart the following:

a user selects a week_year field (CW 44 Year 2016), which is also the Dimension in the Chart, and the last 12 weeks from that selected week_year value will be displayed.

the expresion shall Display the value of the lask weekday of the week.

this formula Displays the correct value, but i still Need to Display all past 12 weeks, and for each week, the value of the last weekday, shall be displayed.

sum(aggr(sum( {$< date_weekyear=, %date_day_index__id ={'$(=aggr(max(%date_day_index__id),date_weekyear))'},

%date_weekyear__id={">$(vL.FromWeek)<=$(vL.ToWeek)"} >} active12_sum) , date_weekyear)

Note the filter and Dimension is the "date_weekyear" field (in figures as "calendar week")

In the figure, for the CW 44.2016, the value 74.845 is not the value of the last day of that week. I Need to get the last weekday value for the current week for all 12 weeks.

Any ideas on how to achieve this?

thanks

Re: get values of weekend dates for several weeks

sunny_talwar — Fri, 02 Dec 2016 18:13:03 GMT

You need Friday's value for the 12 weeks you are seeing?

Sum({$<Weekday = {'Fri'}, date_weekyear=, %date_day_index__id ={'$(=aggr(max(%date_day_index__id),date_weekyear))'},

%date_weekyear__id={">$(vL.FromWeek)<=$(vL.ToWeek)"} >} active12_sum)

Where Weekday field is created in the script like this

WeekDay(Date) as Weekday

I have also removed the Sum(Aggr()) function as I don't see any need to use that

Re: get values of weekend dates for several weeks

felcar2013 — Mon, 12 Dec 2016 12:23:10 GMT

hi, thanks, i used better an if-condition after sum. Before I got the Weekenddate (%date_weekend_day__id) in script. then i got the values for all weeks

final solution looked like this:

sum( {$< date_weekyear=, date_weekyear__id={">$(vL.FromWeek)<=$(vL.ToWeek)"} >}

if(%date_day_index__id=%date_weekend_day__id, active12_sum) )