topic Grouping calculation in QlikView

Grouping calculation

imallears — Thu, 04 Jan 2018 23:12:02 GMT

Hi,

I'm hoping this is simple.

I want to group the Instrument with Start and End Dates, and ultimately calculate the total number of weeks attached to each.

I have attached two screenshots below. The 1st is the raw dataset, and the 2nd is what I want to achieve in Qlikview. How do I do it?? Any help would be great.

Thanks

Dataset

Expected Results

Re: Grouping calculation

vishsaggi — Fri, 05 Jan 2018 00:37:01 GMT

What is the grouping logic you used here? On what criteria you considered Drums which is coming twice?

Re: Grouping calculation

sunny_talwar — Fri, 05 Jan 2018 01:35:24 GMT

Try this

Table:

LOAD Name,

Instrument,

[Start Date],

[End Date],

[number of weeks]

FROM

[..\..\..\Downloads\band data.xls]

(biff, embedded labels, table is Sheet1$);

TempTable:

LOAD If(Name = Previous(Name), If(Instrument = Previous(Instrument), Peek('Sort'), RangeSum(Peek('Sort'), 1)), 1) as Sort,

Resident Table

Order By Name, [Start Date] desc;

FinalTable:

NoConcatenate

LOAD Name,

Instrument,

Sort,

Min([Start Date]) as [Start Date],

Max([End Date]) as [End Date],

Sum([number of weeks]) as [number of weeks]

Resident TempTable

Group By Name, Instrument, Sort;

DROP Tables Table, TempTable;

Re: Grouping calculation

imallears — Sun, 07 Jan 2018 07:51:48 GMT

Excellent. Thank you Sunny