topic Re: Help in Set Analysis - Calculation conditions in QlikView

Help in Set Analysis - Calculation conditions

sheker_amrutham — Sat, 16 Nov 2024 03:44:40 GMT

Hi,

My requirement is to display Sum of SalesAmount for ProdA and ProdB , For ProdA it should calculate Sales Amount for Year is 2016 and Jan, Feb of 2017 and For ProdB it should display Sales Amount of Year 2018. I need to write single expression to display this measure.
Can some one help on this scenario.

Sno	ProductId	SaleDate	SaleAmount
1	ProdA	01-01-2016	250
2	ProdB	01-05-2016	600
3	ProdB	01-09-2016	600
4	ProdA	01-10-2016	260
5	ProdA	02-10-2016	650
6	ProdA	03-10-2016	400
7	ProdA	01-01-2017	600
8	ProdB	01-02-2017	600
9	ProdA	01-02-2017	950
10	ProdB	09-09-2017	500
11	ProdA	01-01-2018	600
12	ProdB	02-01-2018	600
13	ProdB	03-01-2018	260
14	ProdA	04-01-2018	260
15	ProdB	05-01-2018	650
16	ProdB	06-01-2018	500
17	ProdB	07-01-2018	600
18	ProdB	08-01-2018	250
19	ProdA	09-01-2018	600
20	ProdA	10-01-2018	300

Re: Help in Set Analysis - Calculation conditions

bramkn — Fri, 26 Apr 2019 08:40:45 GMT

sum({<ProductId={'ProdA'},SaleDate={">=01-01-2016 <01-03-2017"}>}SalesAmount)+ sum({<ProductId={'ProdB'},SaleDate={">=01-01-2018 <01-01-2019"}>}SalesAmount)
keep in mind it is better to use nums for the dates. and also I use dd-mm-yyyy format

Re: Help in Set Analysis - Calculation conditions

kushalthakral — Fri, 26 Apr 2019 11:07:33 GMT

Hi Sheker

You can use union (+) in set analysis like below

=Sum({<ProductId = {'ProdA'}, SaleDate = {">=01/01/2016<01/03/2017"}> + <ProductId = {'ProdB'},SaleDate = {">=01/01/2018<=31/12/2018"}>}SaleAmount)

and output will be like below

I hope this answers your question

Thanks

Kushal