topic Re: Grouping a 'if' sum expression in QlikView

Grouping a 'if' sum expression

bnelson111 — Sat, 16 Nov 2024 17:53:10 GMT

Below is a table export with name and order which has a Max date greater than so many days. Then if that criteria is achieved then i have below which is working perfectly. However to speed up reporting i would like it grouped.

Thank you in advance.

Please see figure 2

Name	Order	Codes	Max Date	Stock Holding	Dollars
4	221045	52292	14-Apr-20	135,576	£1,351.69
4	222458	18958	10-Jun-20	141,460	£1,404.70
3	222899	60132	27-Jun-20	5,000	£192.70
3	223071	17951	28-Jul-20	61,700	£2,377.92
3	223090	18055	23-Jul-20	27,500	£1,059.85
6	208623	19822	18-Nov-18	94,200	£410.71

Figure 2

Name	Dollars
4	£2,756.39
3	£3,630.47
6	£410.71

The expression is currently

=if([Max Date]<=Today()-180 and CustName='4' or
[Max Date]<=Today()-120 and CustName='3' or
[Max Date]<=Today()-120 and CustName='2' or
[Max Date]<=Today()-120 and CustName='1' or
[Max Date]<=Today()-120 and CustName='5' or
[Max Date]<=Today()-90 and CustName<>'5'and CustName<> '4'and CustName<>'3'and CustName<>'2'and CustName<>'1'

,((sum(InventoryQty)*UnitPrice)/PriceUnitConv))

((sum(InventoryQty)*UnitPrice)/PriceUnitConv)) = Dollars is a expression

CustName = Name currently a Dimension

Also Codes is in Dimension.

Thanks in advance

Re: Grouping a 'if' sum expression

edwin — Tue, 03 Nov 2020 13:50:01 GMT

do you have sample data that shows unit price and price conv? these should be related to products and not orders rt? do these numbers change over time? are they per order? are Codes your product?

Re: Grouping a 'if' sum expression

bnelson111 — Tue, 03 Nov 2020 17:07:30 GMT

Unit price is related to the Codes as well as the unit price, however the orders are dated. The table is to show all order that are over a certain date by Name then value. This is working 100% but i wish then to simplify and sum that name with orders of a date as a over all total, i think the code and date is stopping it.😕

Re: Grouping a 'if' sum expression

bnelson111 — Wed, 04 Nov 2020 10:22:21 GMT

Got it 🙂

=sum(aggr((

if([Max Date]<=Today()-180 and CustName='4' or
[Max Date]<=Today()-120 and CustName='3' or
[Max Date]<=Today()-120 and CustName='2' or
[Max Date]<=Today()-120 and CustName='1' or
[Max Date]<=Today()-120 and CustName='5' or
[Max Date]<=Today()-90 and CustName<>'5'and CustName<> '4'and CustName<>'3'and CustName<>'2'and CustName<>'1'

,sum((InventoryQty)*UnitPrice)/PriceUnitConv)),Codes))