topic Re: cal in QlikView

cal

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

HI ALl

how do we find sum of amount for min date ,

and also this should not affect my slection made in the filter

It whould exclude the selections

Re: cal

effinty2112 — Thu, 20 Jul 2017 09:52:01 GMT

Hi John,

try:

=sum({1<Date = {'$(=min({1}Date))'}>}Amount)

Re: cal

smilingjohn — Thu, 20 Jul 2017 09:55:08 GMT

HI ,

Andrew .....what does 1 do here ? will it exclude all the slections ?

Re: cal

effinty2112 — Thu, 20 Jul 2017 10:11:22 GMT

Hi John,

The expression I gave used set analysis which includes terms inside a set of curly brackets. The number 1 here is a set identifier. The default set identifier is $ which is sometimes omitted, $ means the set of records associated with the current selections. 1 as a set identifier means all records in the app regardless of selections.

The set identifier is used twice here. The first time is to tell QV to disregard all selections when returning the sum of Amounts associated with the minimum date.

The thing to realise is that a selection may change the minimum date. A product that was only bought one week ago will give a minimum date of just a few days ago, so min({1}Date) returns the minimum date regardless of selections.

Cheers

Andrew

Re: cal

mangalsk — Thu, 20 Jul 2017 10:16:46 GMT

1 Represents the full set of all the records in the application, irrespective of any selections made, so this expression given by andrew is working correct

Re: cal

shiveshsingh — Thu, 20 Jul 2017 10:17:14 GMT

try this

=sum({1<Date={$(=min({1}Date)}>}Amount)