topic Re: Set analysis addition in QlikView

Set analysis addition

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

I am trying to use set analysis to add two different balance amounts based off of a filter and a selection.

The addition is correctly working, but the first set analysis function is not returning the right data values.

How the first set analysis should work is: If the Cape Toggle = Only Cape, then we want to take the Current Balance on 4-29 -2016.

The second set analysis is taking the current balance based off the current selection. It looks at the current selection and then goes back to the previous year of the current selection and takes the current balance of that date (so if 10-31-16 is selected, it will look at the greatest date in 2015 which would be 12-31-2016)

=sum({<[Cape Toggle] = {'Only Cape'},[Production Date]={"=2016-04-29"}>}[Current Balance])

sum({$<[Cape Toggle] = {'No Cape'},[Production Date]={"$(=date(max({1<Year={$(=max(Year)-1)}>} [Production Date])))"}>}[Current Balance])

Re: Set analysis addition

Wed, 02 Nov 2016 09:24:40 GMT

Hi Ashley,

for first part of expression

=sum({<[Cape Toggle] = {'Only Cape'},[Production Date]={"=2016-04-29"}>}[Current Balance])

you are using yyyy-dd-mm format for date, is that the format shown for on selection filters?

Re: Set analysis addition

Anonymous — Wed, 02 Nov 2016 15:09:14 GMT

No it is not, should i change it to the way the the selection filter displays?

Re: Set analysis addition

Anil_Babu_Samineni — Wed, 02 Nov 2016 15:21:38 GMT

How is your date format

And try to remove = from first set operator

Re: Set analysis addition

Anonymous — Wed, 02 Nov 2016 15:23:45 GMT

where?

Re: Set analysis addition

Anil_Babu_Samineni — Wed, 02 Nov 2016 15:26:23 GMT

How your date field format looks

And try this

sum({<[Cape Toggle] = {'Only Cape'},[Production Date]= '2016-04-29' >}[Current Balance])

sum({$<[Cape Toggle] = {'No Cape'},[Production Date]={"$(=date(max({1<Year={$(=max(Year)-1)}>} [Production Date])))"}>}[Current Balance])

Re: Set analysis addition

Anonymous — Wed, 02 Nov 2016 15:45:59 GMT

It errors on that expression on the red part:

sum({<[Cape Toggle] = {'Only Cape'},[Production

Date]= '2016-04-29' >}[Current Balance])

Re: Set analysis addition

sunny_talwar — Wed, 02 Nov 2016 15:52:23 GMT

Have you just tried this:

=sum({<[Cape Toggle] = {'Only Cape'},[Production Date] = {"$(=Date(MakeDate(2016, 4, 29)))"}>} [Current Balance])

sum({$<[Cape Toggle] = {'No Cape'},[Production Date]={"$(=date(max({1<Year={$(=max(Year)-1)}>} [Production Date])))"}>}[Current Balance])

Re: Set analysis addition

Anil_Babu_Samineni — Wed, 02 Nov 2016 15:52:30 GMT

Under mOderated try this

Re: Set analysis addition

Anonymous — Wed, 02 Nov 2016 16:05:26 GMT

Thank you this worked perfectly!!!!