topic Re: I like to have an total of more weeks in linechart in QlikView

I like to have an total of more weeks in linechart

francisvandergr — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I like to have totals in linechart from more weeks. Is this possible ?

i included an good example with clear explanation

Re: I like to have an total of more weeks in linechart

swuehl — Fri, 13 May 2016 15:40:20 GMT

Maybe like attached?

Re: I like to have an total of more weeks in linechart

francisvandergr — Tue, 17 May 2016 13:21:33 GMT

It works for my example. Bit in real i use set analyse.

Now i like to use to combine in Set analyses. See all directors but not Director 'AAAA'

Re: I like to have an total of more weeks in linechart

francisvandergr — Tue, 17 May 2016 13:24:17 GMT

See attachment example3

Re: I like to have an total of more weeks in linechart

swuehl — Tue, 17 May 2016 13:25:27 GMT

Not sure what Director 'AAAA' is in your sample data file, can't find a field like this.

Have you tried adding the set expression like

{<Director -= {'AAAA'}>}

to all aggregation functions (and replacing single field references with Only({<Director -= {'AAAA'}>} FieldName) )

Re: I like to have an total of more weeks in linechart

Anonymous — Tue, 17 May 2016 13:26:40 GMT

for Project not AAAA try:

sum({<Project-={'AAAA'}>}Amount)/Count({Project-={'AAAA'}distinct Project)

you may include those which you donot want into a variable and place the variable in expresssion

Re: I like to have an total of more weeks in linechart

francisvandergr — Tue, 17 May 2016 16:36:06 GMT

I get not the right values. You have seen my attachment Example_Simple_3.qvw

Re: I like to have an total of more weeks in linechart

swuehl — Tue, 17 May 2016 16:42:22 GMT

Would be good to see an example with your required results for a chosen director (I've now found the Director field, but no 'AAAA' value).

Maybe like attached using

=Rangesum(Above(sum({$<Director -= {Bert}>}Amount) ,0,Rowno() )) /

rangesum(above(count(aggr( Only({$<Director -= {Bert}>}Project),Project) ), 0 ,RowNo() ))

Re: I like to have an total of more weeks in linechart

francisvandergr — Thu, 19 May 2016 12:24:40 GMT

Here i send you my original qvw. I describe my problem in two steps on the 2 worksheets.

I think we are close but we have not the result we want. I hop you can help me

Re: I like to have an total of more weeks in linechart

swuehl — Thu, 19 May 2016 12:33:57 GMT

On the first sheet, try

(Rangesum(Above(sum({$<Project_Projectleider -= {'Bert Schilt'}>}Project_Aanneemsom) ,0,Rowno() )) +

Rangesum(Above(sum({$<Project_Projectleider -= {'Bert Schilt'}>}Meerwerk_Bedrag) ,0,Rowno() )))

rangesum(above(count(aggr(Only({$<Project_Projectleider -= {'Bert Schilt'}, Project_Code = {"=Sum ({<Jaar = {$(=max(Jaar))}>}Project_Aanneemsom)"}>} Project_Code), Project_Code)) , 0 ,RowNo() ))

I must admit I haven't fully understood the issue on sheet 2.

Re: I like to have an total of more weeks in linechart

francisvandergr — Thu, 19 May 2016 12:54:00 GMT

The problem on sheet 2 is that i get anouther value for week 1 as on sheet 1 . I do not understand why. I have the same problem with the formula you just send me.

Re: I like to have an total of more weeks in linechart

swuehl — Thu, 19 May 2016 13:45:52 GMT

Have a look the attached sample, using an additional AsOf table

The As-Of Table

Re: I like to have an total of more weeks in linechart

francisvandergr — Thu, 19 May 2016 14:24:36 GMT

Thnx Swuehl, almost when i have 3 weeks i still have a problem. I attached an example

Re: I like to have an total of more weeks in linechart

swuehl — Thu, 19 May 2016 14:34:59 GMT

Well, you would need to adapt the advanced search for the Project_Code filter to the calculations you consider when telling me

3. Here 10 Projects because i see also projects with 0

I can only assume you want something like

count( {$<Project_Projectleider -= {'Bert Schilt'},Project_Code = {"=Sum ({<Jaar = {$(=max(Jaar))}>}Rangesum(Project_Aanneemsom,Meerwerk_Bedrag))"},AsOfWeek = p(Week)>} DISTINCT Project_Code)

Re: I like to have an total of more weeks in linechart

francisvandergr — Thu, 19 May 2016 15:17:32 GMT

Yes ! for those weeks it is right but at week six it goes wrong again:

Details gives AVG1.370.598 / 69 = 19864

AVG line gives 20155.86.

And also i like to have a line for 2015 to compare for the same period

Re: I like to have an total of more weeks in linechart

francisvandergr — Thu, 19 May 2016 15:30:44 GMT

Sorry mistake from me till week 6 it is indeed ok this is what i want.

Now i only want to compare with 2015.

Is this possible with QlikView in this purpose?

Re: I like to have an total of more weeks in linechart

swuehl — Thu, 19 May 2016 15:46:12 GMT

Try to adapt your set expressions accordingly.