How to get individual sum value for range based on SID .

MEDHA07 — Sat, 21 Jan 2023 18:27:55 GMT

Hi All,

I am having data based on location IDs .

I have multiple location ids and there are two location ids having same poldimid with different values.

I need to show data based on Range selection like bucket

=<5m

>5M to <=10M

>10M to <=15 M

Location id123

Value 1: 5000000

Value 2 :12 M

Location id 123 is having data for =<5 M count is 1

Then >10 M Count is 1

The other location ID 456 :

value 1:2M

Value 2 : 6 M

Location id 456. Will come in <=5 M range and count is 1

Second value should come in >5 M range

I am showing data in table for all location ids by default.

The count is coming and total sum values are coming and range also displaying correct.

When i select individual location ids data is coming in one range is correct and other one is dispalying same range or different range based on total value

For particual this scenario its coming same range but total value is wrong.its showing two different location sids sum in range .it snot showing individual sum

If we select these location ids once at a tine in filter data is displaying correct.

The poldim id available in these two locations and i am doing group by sic and poldimid

Values are differ for individaul location ids based in subject id which is unique in table.

When we select individual location ids the total is calculating over all sum for based on poldimid

In both selections its showing total sum for that poldim id range

But i should get individual sum

Main table:

Sic&poldimid as Key,

Pln no,

LicNo,

Poldimid,

Locationid,

Subjdid,

Sic,

Values,

Dept;

Resident:

Load

Sic&poldimid as Key,

Rowno() as Countofpols,

Sum(values) total

Resident t1

Group by

Sic,poodimid;

I must do group by onlh Soc and poldimid

Resulsts:

All locationids values apperaing correct in range

For individual for locationid 123

<=5M. 5000000

>5M to <=10M. 0

>10M to <=15 M. 14 M

but actual values is 12 M AND 2M Is from location 456 value since it has same poldim id

It is showing total sum value based on poldimid

But i need to get only 12 M value in thta range

If we select 456 location its showing 14 M value for

But actual value should dispay 2 M under <=5 M range

Re: How to get individual sum value for range based on SID .

jcmachado — Wed, 25 Jan 2023 21:12:37 GMT

It sounds like you are having an issue with the group by statement in your Resident table.

It appears that the group by statement is only grouping by Sic and Poldimid, which is causing the values from different location ids with the same Poldimid to be combined in the same range.

To fix this, you can add the Locationid field to the group by statement in your Resident table. This will ensure that the values for each Locationid are grouped separately, and the ranges will be calculated correctly for each individual Locationid.

Here's an example of how you can modify the Resident table:

Resident: Load Sic&poldimid as Key, Rowno() as Countofpols, Sum(values) total Resident t1 Group by Sic,poodimid, Locationid;

With this modification, the Resident table will group the data by Sic, Poldimid, and Locationid, which will ensure that the values for each Locationid are calculated separately, and the ranges will be correct for each individual Locationid.

It could be that you need to do a join on the table with your main table to get the correct values, or maybe you need to do a sub-set of the data, that you want to see in the table.

Please note that this is just an example, you might want to adjust the formula according to your data and needs.

topic How to get individual sum value for range based on SID . in QlikView

How to get individual sum value for range based on SID .

Re: How to get individual sum value for range based on SID .