Newest 'set-analysis' Questions - Stack Overflow

Questions tagged [set-analysis]

The tag has no usage guidance.

-1
votes
1answer
7 views

QlikView Expression that ignores some selections and contains where explicit values

I have a basic expression SUM(value). I want to modify this expression so it does 2 things: Ignores all list box selections except one (yearperiod) and contains explicit where conditions. Pseudo ...
0
votes
1answer
7 views

QlikView Default selection trigger - previous + current year minus current month

I want to create a sheet trigger to select the previous year and the current year but not the current month. If for example we were in 201604... Sample data is as follows: 201511 201512 201601 ...
0
votes
1answer
12 views

QlikView aggr more than one value excluded in list box

I have a list box that is powered by an expression to hide 1 value. =aggr(only({<[name]-={John}>}[name]),[name]) But i want to add a second name to this. I have tried... =aggr(only({<[name]...
0
votes
1answer
9 views

QlikView Using Accumulate 12 steps back for MAT graph, but cant limit date range

I have a line chart which has two dimensions: Yearperiod = 201601, 201602, 201603 etc Acgroup = FAB, BOB, TOE, QUE etc And one expression: SUM(Quantity) This also has Accumulate 12 steps back ...
1
vote
1answer
11 views

Qlikview use IN rather than = in a variable in an expression

I have a variable in an expression that SELECTS bottles. sum({$<[PCC] ={"$(=concat([MCC],'","'))"},[RCPT] ={'Bottle'}>}[Amount]*-1) I would like it to be able to select where RCPT equals ...
1
vote
1answer
8 views

QlikView expression for last 12 months & DISTINCT & not changed by selections

I have a few expressions: =COUNT(SUPP) Gives me 9316 which is correct as it is the total number. =COUNT(DISTINCT SUPP) Gives me 3429 which is correct as it is the total distinct number. =ROUND(...
0
votes
1answer
24 views

QlikSense Set analysis SUM expression with explicit less than and ignore selections

I have an expression in QlikSense: Sum({$<YEAR=,YEAR= {$(=YEAR(TODAY())-1)},MONTH={$(=NUM(Month(today()),'#'))}>}MOVES) Which gives me the SUM of MOVES from 2018 (previous year) in MONTH 6 (...
0
votes
0answers
15 views

How can I have the date just before the max/last Date and repeat the process on the whole table?

I'm trying to calculate the delta between two values in different dates : the firt is the date Max(COD) ( Sum({<COD={"$(=Max(COD))"}>*<Contrat={'PRESTA'}>} if([FMonth.autoCalendar.Num]&...
-1
votes
1answer
56 views

set analysis expression distinct count with multiple exclusions

I am trying to display a unique count of people. There are multiple programs that the people could have been in, and some of them I don't want to be counted (they were only on a wait list). I've ...
0
votes
0answers
27 views

Chart dimension selecting KPI even with set analysis

I have a KPI with the following expression: count({<[Status]={'Active'},Month=>} user_id) Also a bar chart where: Dimension: Month Measure: count(user_id) And finally a filter for the ...
1
vote
1answer
71 views

Show Customer Orders that also had orders yesterday

I am trying to create a formula that will show all orders for customers that have an order yesterday. So if the customer has an order yesterday, show all orders for that customer but if a customer ...
0
votes
1answer
110 views

QLIKVIEW 11: Formula works on Text Object but not in LOAD SCRIPT

I have the following formula in a Text Object: =Num(Sum(Aggr(Count({<Jahr={$(vTodayYear)}, Kw={">=1<=$(vTodayKw)"}, Database.Kennzahl={'Ew'}, Database.Szenario={'Actual'}>} DISTINCT ...
0
votes
1answer
142 views

QlikSense - Set Analysis - Handling complexities - Arithmetic, Fields, Variables, Variables within variables, Greater than etc

I am somewhat new to QlikSense, but am getting a hang of it. Set Analysis is probably my weak spot and no matter how much I read, I tend to forget everything within hours. Plus, the guides don't do a ...
-1
votes
1answer
577 views

Color Coding KPI using Set Analysis and simple comparison functions (Qlik)

New Qlik Sense/ Qlik View user here. I want to create a KPI, where the conditional coloring will filter based on two fields: EstArrivalTimeHrMinSec [Estimated time taken for an Ambulance to ...
1
vote
1answer
585 views

Get average of all the table's average - Qlikview

Is it possible to get the average value of each straight table and get the average again? Example: Avg(1.99%+3.66%+3.49%+2.26%+2.14%+2.61%+5.54%+3.11%+2.92%+1.06%)
0
votes
1answer
133 views

Trend chart in Qlik Sense not changing on user selections after applying set analysis on the trend chart

I have a trend chart in Qlik Sense where I have applied the following condition to filter the data : avg({$<DATA_SUBJECT_NAME={'CALL','ACCOUNT','IDENTITY','FINANCIALTRANSACTIONS','HOUSEHOLD','...
0
votes
1answer
42 views

In Qlikview, Find no of stores that have achieved the budget

In Qlikview, I am trying to get no of stores that have achieved budget for this year to date. I run the report every day morning for the year upto end of yesterday. The expression below is not ...
0
votes
1answer
626 views

Qlikview Moving Average

I have to create a stock turn report in Qlikview. For this report, I need to calculate the moving average of weekly stock based on last 13 weeks. I can acheive this by the expression below: RangeAvg(...
0
votes
1answer
367 views

Set analysis formula in table does not work properly

I have a table with formulas using set analysis syntax. Like so: ((RAWP_PRICE-ONLY({1<RAWP_YEARMONTH='$(Auswahlperiode)'}>}RAWP_PRICE))*ARRO_AnteilROAR_in_%/100) /Sum({$<ARRO_PREISPERIODE={'$...
0
votes
1answer
452 views

Set analysis - ignore current row in pivot

Consider that I have a table of salesman and I would like to know the average value if we do not count the current salesman: |Salesman|sum(sales)| avg[other](sales)| |-------------------|-------------...
0
votes
1answer
239 views

Qlikview Set Analysis Average of the Minimum

I am trying to get the average of the minimum dates in a qlikview set analysis. There is a set of data with an Initial Contact date, which is then followed by many Meeting Dates. What I wanted to ...
0
votes
3answers
621 views

Aggregate other rows based on comparing dimension in Set Analysis

I have data that looks like this: Associate ReferredBy SalesQty ReferralSales Alice Bob 50 Bob Carl 60 Carl NULL 70 Alice NULL 80 Bob ...
0
votes
1answer
382 views

Qlikview ignore selection in text box (object)

I have a text box that contains an expression, however i dont want it to be affected by the selections in the list boxes. =SUM(IF( (date(Monthname(C_DATE),'MMM-YY')) >=(AddMonths(Today(),-12)),1,0)...
0
votes
1answer
402 views

Qlikview Count of ids in last 12 months in text object (box)

I have two fields in my table Date & ID, i want to put in a text box the count of all the ids for the last 12 months. This returns nothing: =Num(count({$<DATE={'>=$(=AddMonths(Today(),-12))...