topic Re: Exclude rows based on set analysis value in QlikView

Exclude rows based on set analysis value

Wed, 13 Apr 2016 14:41:28 GMT

I have a sample list of jobs with a spend value per job based on a previous days snapshot and today's snapshot. I need to exclude all rows of data which have 0 in the variance column

Yesterdays Amount uses the following Set analysis - sum({<rundate ={"$(vPreviousRunDate)"}>} total_amount)

Today's Amount uses the following Set analysis - sum({<rundate ={"$(vCurrentRunDate)"}>} total_amount)

Variance uses the following set analysis - sum({<rundate ={"$(vCurrentRunDate)"}>} total_amount) - sum({<rundate ={"$(vPreviousRunDate)"}>} total_amount)

Booking Date	Job No	Yesterday's Amount	Todays Amount	Variance
01/03/2016	1	11413	4757	6656
02/03/2016	2	6319	6319	0
03/03/2016	3	14887	4749	10138
04/03/2016	4	7529	7529	0
05/03/2016	5	11196	3734	7462
06/03/2016	6	2166	2166	0
07/03/2016	7	2117	920	1197
08/03/2016	8	2508	2508	0
09/03/2016	9	6854	6548	306
10/03/2016	10	14773	271	14502
11/03/2016	11	12304	9596	2708
12/03/2016	12	7192	7192	0
13/03/2016	13	829	1460	-631
14/03/2016	14	3810	3810	0
15/03/2016	15	12268	564	11704
16/03/2016	16	12144	2310	9834
17/03/2016	17	606	606	0
18/03/2016	18	2464	2464	0

Any help would be appreciated.

Re: Exclude rows based on set analysis value

Chanty4u — Wed, 13 Apr 2016 16:00:03 GMT

chk dis

Excluding values in Set Analysis

Re: Exclude rows based on set analysis value

Wed, 13 Apr 2016 16:07:54 GMT

how to I incorporate this into my set analysis which is used in the variance column

Re: Exclude rows based on set analysis value

Gysbert_Wassenaar — Fri, 15 Apr 2016 10:11:06 GMT

Try using some helper variables:

Variables:

vAmountPrev: sum({<rundate ={'$(vPreviousRunDate)'}>} total_amount)

vAmountCur: sum({<rundate ={'$(vCurrentRunDate)'}>} total_amount)

vVariance: rangesum($(vAmountCur),-$(vAmountPrev))

Chart expressions:

=sum({<rundate ={"$(vPreviousRunDate)"}, [Job No]={"=$(vVariance)<>0"}>} total_amount)

=sum({<rundate ={"$(vCurrentRunDate)"}, [Job No]={"=$(vVariance)<>0"}>} total_amount)

=$(vVariance)

Re: Exclude rows based on set analysis value

Fri, 22 Apr 2016 11:51:32 GMT

I couldn't get your solution to work properly. the variables seem to have calculated the variance between the total data set. I need to compare the total amounts for the individual job between the 2 different rundates

Re: Exclude rows based on set analysis value

sunny_talwar — Fri, 22 Apr 2016 14:52:02 GMT

Like this?

Expressions:

If(Sum({<rundate ={"$(vCurrentRunDate)"}>} total_amount) - Sum({<rundate ={"$(vPreviousRunDate)"}>} total_amount) <> 0,

Sum({<rundate ={"$(vPreviousRunDate)"}>} total_amount))

If(Sum({<rundate ={"$(vCurrentRunDate)"}>} total_amount) - Sum({<rundate ={"$(vPreviousRunDate)"}>} total_amount) <> 0,

Sum({<rundate ={"$(vCurrentRunDate)"}>} total_amount))

=sum({<rundate ={"$(vCurrentRunDate)"}>} total_amount) - sum({<rundate ={"$(vPreviousRunDate)"}>} total_amount)

Re: Exclude rows based on set analysis value

Gysbert_Wassenaar — Fri, 22 Apr 2016 14:58:15 GMT

Don't put an = character in front of the expression in the variable if I didn't. And change the double quotes in the variables to single quotes:

Variables:

vAmountPrev: sum({<rundate ={'$(vPreviousRunDate)'}>} total_amount)

vAmountCur: sum({<rundate ={'$(vCurrentRunDate)'}>} total_amount)

vVariance: rangesum($(vAmountCur),-$(vAmountPrev))