Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
brunico59
Contributor
Contributor

set analysis and dollar sign expansion: different results according to selection

Hi all

I consulted several documents, discussions and posts, but I didn't find a solution

I'm performing a count of events (reservations for clinical exams) based on periods between different agenda's definitions

in my dataset, I have, among others, the four data involved in this set analysis: the id of the agenda (there are >500 different agendas) the Id the slot, date and time of reservation, date of agenda definition (Cod_Agenda, ID_SLOT, DATA_ORA_PRENOTAZIONE, DATA_GENERAZIONE PERIODO). The amount of data is quite huge (300.000 rows)

Now, for each agenda, i wish to know how many reservations have been done after each agenda definition. My analysis is based on dta of a six months period. Agendas definition is performed in different days for each agenda and there aree too many dates to setit as a dimension. So my dimensions are based on the agenda id (Cod_Agenda) and related issues, suc ad branch.

first of all I got, for each agenda, the latest DATA_GENERAZIONE_PERIODO, the 2nd latest and the3rd latest

Now I wish to answer my question.

To do this i needed to overcome the difference in data format between DATA_GENERAZIONE_PERIODO and DATA_ORA_PRENOTAZIONE. I solved using a variable (CompAggiorPrec), which generates for each value the date and time format

then i used the set analysis. I tried a lot of different ways and or they give 0 value (wrong ftatemts, discarded), or they give me a value that is either correct or wrong depending on the selection.The following is one of them (I found four different but equivalent ways to get this result)

variables

CompAggiorPrec: If (Max (DATA_GENERAZIONE_PERIODO, 2)>0, '">"&Max(DATA_GENERAZIONE_PERIODO, 2)&' 12:00:00"',Null())

check expression

$(CompAggiorPrec), which gives the date and time in number/texyt format, e.g 14/06/2018 12:00:00



expression

Count( { $ < DATA_PRENOTAZIONE_SLOT= {">$(=$(CompAggiorPrec))"} >} ID_SLOT)

This way, or one equivalent, if do no selection I get a wrong result, if I select the branch, the result is approximate, if I select the agenda, it is right. But agenda are >500.


no selection:

Branch

Cod_Agenda

Slots

Reserv

R in period

Agenda's Changes

Last Change

2ndLast

3rdLast

in period > 2nd last

AG

AG026-1

85

76

55

0

27/02/18

-

-

55

AG

AG204-1

128

108

88

1

24/04/18

17/01/18

-

88

AG

AG216-1

397

332

296

3

03/08/18

14/06/18

17/05/18

36

selection:

Branch

Cod_Agenda

Slots

Reserv

R in period

Agenda's Changes

Last Change

2ndLast

3rdLast

in period > 2nd last

AG

AG026-1

85

76

55

0

27/02/18

-

-

55

AG

AG204-1

128

108

88

1

24/04/18

17/01/18

-

88

AG

AG216-1

397

332

296

3

03/08/18

14/06/18

17/05/18

162

I red hundreds of pages on text and value in dates, data format, variables, dollar sign expansion, set modifiers, and I found no solution.

Many thanks for helping

5 Replies
neelamsaroha157
Specialist II
Specialist II

Can you share a sample data with desired output to work on?

brunico59
Contributor
Contributor
Author

I try in some minute

brunico59
Contributor
Contributor
Author

Well, I apologize.

I'm not able to find how to annex a file

rwunderlich

When replying, click "Use Advanced Editor" in the upper right corner.  Then you will have an "attach" option in the lower right.

-Rob

rwunderlich

You cannot use Set Analysis for this case as both DSE and Set expression is evaluated only once per chart and you need to use a different value per Agenda.  You will have to use an count(if()...) instead.

-Rob