Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Count instances of the same total

Hello;

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

ItemPrdPrdTotalDistinct TotalsRequired
Fuel1819-01-£29861
Fuel1819-02-£24065
Fuel1819-03-£24065
Fuel1819-04-£24065
Fuel1819-05-£24065
Fuel1819-06-£24065
Fuel1819-07-£19061
Fuel1819-08-£13061
Fuel1819-09-£11063
Fuel1819-10-£11063
Fuel1819-11-£11063
Fuel1819-12-£9361
Lease1819-01-£171112
Lease1819-02-£171112
Lease1819-03-£171112
Lease1819-04-£171112
Lease1819-05-£171112
Lease1819-06-£171112
Lease1819-07-£171112
Lease1819-08-£171112
Lease1819-09-£171112
Lease1819-10-£171112
Lease1819-11-£171112
Lease1819-12-£171112

 

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

3 Replies
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Specialist III
Specialist III

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
;
Highlighted
Specialist III
Specialist III

commQV07.PNG