topic Re: Yearly expression calculation in QlikView

Yearly expression calculation

yvonne-c — Wed, 25 Nov 2020 16:16:04 GMT

Sum({<[Target Weight]={600},Class-={Nil},Date>}Aggr(Sum({<Date>}Quantity)/Only({<Date>}[Calculated Cow Course])/Only({<Date>}RangeAvg(NumCows15,NumCows14))/209,Product,ReferredTo,Year))

I am using the above expression to create the following chart

Instead of calculating on a range of the years '/Only({<Date>}RangeAvg(NumCows15,NumCows14))/' I want each column to divide by the NumCow figure for that year

Can anybody help me with the expression for this?

I have a NUMCOW figure for each year

or- even better could it be done from a list of individual animals - a count of all the animals for all the years in a list (with a field 'count date')

Thanks

Re: Yearly expression calculation

sunny_talwar — Wed, 05 Oct 2016 16:24:48 GMT

It should already be doing that, is it not? Can you share a sample with the expected output you wish to see?

Re: Yearly expression calculation

yvonne-c — Thu, 06 Oct 2016 07:54:09 GMT

I don't want to divide by an average of the years, I want to divide by the number of cows in each year

so I have the no of cows in a table like this:

Farm	Nocows15	Nocows14
Farm A	141	132
Farm B	439	247
Farm C	158	182
Farm D	157	156

Sum({<[Target Weight]={600},Class-={Nil},Date>}Aggr(Sum({<Date>}Quantity)/Only({<Date>}[Calculated Cow Course])/ Only({<Date>}RangeAvg(NumCows15,NumCows14))/209, (I want this part to divide by the Total of Nocows15 for 2015 or total of Nocows14 for 2014 and so on)

Product,ReferredTo,Year))

Does that make sense?!

Thanks

Re: Yearly expression calculation

sunny_talwar — Thu, 06 Oct 2016 17:05:51 GMT

I am still not certain that I understand what you want, can you may be share you qvw to look at?

Re: Yearly expression calculation

yvonne-c — Thu, 10 Nov 2016 08:39:33 GMT

Sum({<[Target Weight]={600},Class-={Nil},Date>}Aggr(Sum({<Date>}Quantity)/Only({<Date>}[Calculated Cow Course])/Sum({<[Age, months]={">24"},Year=>}(Totalanimal)),Product,ReferredTo,Year))

I've nearly got this to work with the above expression, using a list of animals.

Sum({<[Age, months]={">24"},Year=>}(Totalanimal)),

The problem I have now is that its dividing by ALL of the Totalanimal, I just want it to divide by the sum of those animals with a 'Countdate' in that year

Does that make sense now?!

Thanks

Re: Yearly expression calculation

Anil_Babu_Samineni — Thu, 10 Nov 2016 17:10:22 GMT

Can you explain more with dummy data and expected o/p. So that, We might get clue to solve your issue