topic Re: Timeframe Data Calculation in QlikView

Timeframe Data Calculation

Thu, 28 Aug 2014 16:47:08 GMT

Hi all,

I am currently working with Actual and Forecast data for labor headcount, displayed by Month and Week.

My issue is: I use the SUM function to pull the data into my charts and tables, and it duplicates the Forecast correctly at the Weekly view for comparison to Actual. However, headcount is reported and forecast at a Monthly level, so I do not want it to sum all of the weeks value's when I drill up to Month. I only want it to show me the headcount from the final week in that month. Does anyone have any suggestions for how I can accomplish this?

Ex.

Dept Wk Mo Yr Cnt

129 1 1 2014 5

129 2 1 2014 5

129 3 1 2014 5

129 4 1 2014 6

So for the month the Cnt should be 6 for Dept 129 for Mo = 1 Yr = 2014 as that is the last week in the month. But for the monthly display, it should show as above.

Thanks

Re: Timeframe Data Calculation

phaneendra_kunc — Thu, 28 Aug 2014 18:29:55 GMT

See attached.

Re: Timeframe Data Calculation

Thu, 28 Aug 2014 18:48:16 GMT

Thanks, Phaneendra. My only problem is that financially, there might be either 4 or 5 weeks in a given Financial Month. So I am not able able to simply limit the set analysis to selecting Wk 4. Instead, I would like to have it select the Max Wk within the selected Month. Is there any way to do that?

Re: Timeframe Data Calculation

rwunderlich — Thu, 28 Aug 2014 18:55:21 GMT

In the Month display use:

=FirstSortedValue(Cnt, -Wk)

-Rob

Re: Timeframe Data Calculation

phaneendra_kunc — Thu, 28 Aug 2014 18:58:05 GMT

Rob's Expression will do for dynamic calculation.

Re: Timeframe Data Calculation

Thu, 28 Aug 2014 19:08:26 GMT

By "the Month display", where do you mean for me to place that expression? e.g. within the set analysis itself, within properties > dimensions, ...?

Re: Timeframe Data Calculation

rwunderlich — Thu, 28 Aug 2014 19:21:32 GMT

I understood you wanted the last week value only when showing data at the Month level, not the weekly level. However, I think you can probably ignore my comment. Just use the expression I suggested as your Cnt Expression in the chart. It should work for both levels of detail.

-Rob

Re: Re: Timeframe Data Calculation

phaneendra_kunc — Thu, 28 Aug 2014 20:39:57 GMT

As Rob said just use =FirstSortedValue(Cnt, -Wk) as expression.

Refer to the example below.

Re: Re: Re: Timeframe Data Calculation

Fri, 29 Aug 2014 18:13:50 GMT

So if we actually need it to be a little different because we have an employee ID in this example that really has a count of 1 but then is summed to get the week total, how would you do that calc. See attachment below.

So instead of seeing the 8 and 6 in the last column I really want to see 1 and 4 as those are the counts for the last week of each month?

Thanks!