How to calculate sum of sales based on minimum value of another field in Qlikview?

qlikview

#1

Hi,

I have developed a sales dashboard, here I want an another metric to show the sum of sales on the first day of current selection. Like, if current selection is “Product AAA” then it must show the sum of sales for “Product AAA” on the first day of sale based on Pol_Date.

Below is the example table structure and I know it can be performed using Set Analysis but not sure how to access minimum date in set analysis and generate this expression.

Pol_ID	Pol_date	Product   Sales
P001	05/12/2014	AAA        4567
P002	04/03/2014	BBB        6789
P003	04/03/2014	AAA        3243
P004	05/03/2014	BBB        8967
P005	06/01/2014	CCC        7865
P006	06/01/2014	BBB        3456

Please help me to perform this.

Regards,
Karan


#2

@Karan

You can perform this by using DOLLAR sign expansion with MIN function within set analysis expression. Date must be in date format if it is not, use conversion function also. I am assuming that it is in date format.

Below Set Analysis expression will solve your requirement.

=Sum({<Pol_date={'$(=(Min(Pol_date)))'}>}Sales)

You can also refer below article for more information on Set Analysis.

Hope this helps!

Regards,
Sunil