Count instances of the same total

[Shiman0]

Hello;

I've been wrestling with being able to count the number of times (Prd) a total value occurs within each category (Item)

Item	Prd	PrdTotal	Distinct Totals	Required
Fuel	1819-01	-£298	6	1
Fuel	1819-02	-£240	6	5
Fuel	1819-03	-£240	6	5
Fuel	1819-04	-£240	6	5
Fuel	1819-05	-£240	6	5
Fuel	1819-06	-£240	6	5
Fuel	1819-07	-£190	6	1
Fuel	1819-08	-£130	6	1
Fuel	1819-09	-£110	6	3
Fuel	1819-10	-£110	6	3
Fuel	1819-11	-£110	6	3
Fuel	1819-12	-£93	6	1
Lease	1819-01	-£171	1	12
Lease	1819-02	-£171	1	12
Lease	1819-03	-£171	1	12
Lease	1819-04	-£171	1	12
Lease	1819-05	-£171	1	12
Lease	1819-06	-£171	1	12
Lease	1819-07	-£171	1	12
Lease	1819-08	-£171	1	12
Lease	1819-09	-£171	1	12
Lease	1819-10	-£171	1	12
Lease	1819-11	-£171	1	12
Lease	1819-12	-£171	1	12

PrdTotal in a variable: vCatPrdTotal defined as Aggr(sum(Value), Item,Period)
Both the below give same result; being number of distinct period totals....
Aggr(Count(DISTINCT Item & $(vCatPrdTotal)),Item) 
Count(TOTAL <Item> Aggr(Item,Period,$(vCatPrdTotal))) 

What I cannot get is the 'Required' value: number of instances of the same PrdTotal
Thought it would be something like Aggr(Count( $(vCatPrdTotal)), Item & $(vCatPrdTotal) ), but everything I've tried either results in nothing or something odd..

Grateful for your help....

Bob

[Brett_Bleess]

Hey Bob, about the best I can do is the following Design Blog post, and I will include the base URL to the area below that in case you want to search further yourself.  There are 700+ posts in this area, mostly how-to that may come in handy.

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have something better, and there are some further AGGR related posts just FYI, but the one I gave you is probably the most pertinent for starters.

Regards,
Brett

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    Item, Prd, PrdTotal, Distinct Totals
    Fuel, 1819-01, -£298, 6
    Fuel, 1819-02, -£240, 6
    Fuel, 1819-03, -£240, 6
    Fuel, 1819-04, -£240, 6
    Fuel, 1819-05, -£240, 6
    Fuel, 1819-06, -£240, 6
    Fuel, 1819-07, -£190, 6
    Fuel, 1819-08, -£130, 6
    Fuel, 1819-09, -£110, 6
    Fuel, 1819-10, -£110, 6
    Fuel, 1819-11, -£110, 6
    Fuel, 1819-12, -£93, 6
    Lease, 1819-01, -£171, 1
    Lease, 1819-02, -£171, 1
    Lease, 1819-03, -£171, 1
    Lease, 1819-04, -£171, 1
    Lease, 1819-05, -£171, 1
    Lease, 1819-06, -£171, 1
    Lease, 1819-07, -£171, 1
    Lease, 1819-08, -£171, 1
    Lease, 1819-09, -£171, 1
    Lease, 1819-10, -£171, 1
    Lease, 1819-11, -£171, 1
    Lease, 1819-12, -£171, 1
];

Left Join(tab1)
LOAD Item, PrdTotal, Count(DISTINCT Prd) As Required
Resident tab1
Group By Item, PrdTotal
;

[Saravanan_Desingh]