How to sum over the month in the backend

[rkpatelqlikview]

Dear Qlik,

PFA sample QVW.

How to do sum calculation in the backend. The values should be changed in the front end like Data as shown in the  Pivot Table.

Thanks

KUMAR

[niclaz79]

Hi,

You use a

Sum(Value) as Value

and then

Group by

However in your model you are connecting a date to the calendar, but you want the sums to be on Month? Then you should change this connection to connect to Month instead.

[rkpatelqlikview]

Hello Niclas,

Yes, I need date for other tables as well.

I need to get the total values of "OVSD, HB, HA, CD'" and total kms travelled by the DriverName.

If i select the Month then it should be changed.

Like PIVOT table.

[rkpatelqlikview]

How the values are changing in the PIVOT table. The same changes to be happened in table box with backend fields.

Thanks for your support.

[sunny_talwar]

Have not even looked at your qvw, but what you are asking for is just not possible. Script create static fields and these values can not change based on selection.

[rkpatelqlikview]

Thanks Sunny.

PFA qvw. Now i selected Drivername Abdul Rasheed. In Pivot table his values Score and Category is 18..., Green. If you selects the month the value and category will be change.

Here these expressions can i write in the backend ?  and if i take the Drivername and that fields in Table box these values should be changed like PIVOT.

I did some script in the backend with group by. I think need to be changed. thanks for ur support

[rkpatelqlikview]

Thanks for your support Sunny. Am very sorry my way of approach to understanding the task might be wrong.

Can you please open this attached qvw.

I mentioned in the text object. Thanks

[sunny_talwar]

So, you want to aggregate on Driver Name, Vehicle and Month? Is this needed in addition to non aggregated data or aggregated data replaces the non aggregated data?

[rkpatelqlikview]

Hi sunny,

It should be replace the non aggregated data with aggregate data. Yes. Drivername , Vehicle,month.

Thanks for your help

[rkpatelqlikview]

Thanks sunny,

This is the equation am using in front end and back end this same logic i need to implement for aggregation of entire month and Driver.

so that in frond end I can take Bottom Red, Yellow Drivers and top Green Drivers and need show count of all Green, Yellow, Red Drivers.

=If(((sum([HB Count])+sum([OVSD Count])+sum([CD Count])+sum([HA Count]) )/ sum([Kms Travelled]) *1000) <=20, 'Green',

      If(((sum([HB Count])+sum([OVSD Count])+sum([CD Count])+sum([HA Count]) )/ sum([Kms Travelled]) *1000) >20 and

      ((sum([HB Count])+sum([OVSD Count])+sum([CD Count])+sum([HA Count]) )/ sum([Kms Travelled]) *1000) <=50, 'Yellow',

      If(((sum([HB Count])+sum([OVSD Count])+sum([CD Count])+sum([HA Count]) )/ sum([Kms Travelled]) *1000) >50, 'Red')))