topic Re: Selection change data.. in QlikView

Selection change data..

qlikviewaf — Tue, 12 Sep 2017 13:49:30 GMT

Hi Guys,

as you can see below:

if i select just 2017 i got as budget 6640 which is correct,

if i select 2017 + AREA= Emea Distributor i got 6614 which is wrong

Anyone know why? i'm driving crazy...

find attached also the application

Re: Selection change data..

prieper — Tue, 12 Sep 2017 13:58:54 GMT

not all areas are mapped to region ...

Re: Selection change data..

qlikviewaf — Tue, 12 Sep 2017 14:02:38 GMT

Everything is mapped, except "Corporate Adjustment" but it's ok

Re: Selection change data..

prieper — Tue, 12 Sep 2017 14:12:54 GMT

There is a calculation in it for calculating the budget. If you remove the

$(vWorkingDays)*$(vWorkingDaysSelected)

calculation seems to be fine.

Think that the error is within the logic of these variables, as values are calculated irrespectively your selections.

Re: Selection change data..

qlikviewaf — Tue, 12 Sep 2017 14:14:11 GMT

I found that if i select Japan and Emea_Distributor i got the right amount, but honestly i don't get why i have to select both

Re: Selection change data..

prieper — Tue, 12 Sep 2017 14:26:40 GMT

Dear Antonio,

when you select EMEA_Distributor, the variable vWorkingDaysSelected changes from 260 to 259,

thus sum of Budget will be multiplied not with 260/260 but with 260/259.

Re: Selection change data..

qlikviewaf — Tue, 12 Sep 2017 14:58:57 GMT

Good point, but why? i set the variable in order to not consider the AREA selection.

=Sum({<Area=,Region_Description=,Region=, Month=, Quarter=, Week=, Day=,Year=,Invoice_Date=, Invoice_Date_Num={">=$(=Num(Min(Invoice_Date_Num)))<=$(=Max(Invoice_Date_Num))"}>} WorkingDay)

Re: Selection change data..

prieper — Wed, 13 Sep 2017 08:53:22 GMT

The variable is still calculated within the dataset, so for Spain the minumum data is 03/01, whilst for all others it it 02/01.

Am not that firm with SET and there might be other and better solutions, but suggest to create a variable

vYearSelected = GETFIELDSELECTIONS (Year) // this way may work with multiple selections

and revise

vWorkingDaysSelected to

=SUM({1 <Year = {$(vYearSelected)}>} WorkingDay)

This variable would Sum all workingdays in the entires dataset, only considering the selection of the Year.

Peter

Re: Selection change data..

qlikviewaf — Wed, 13 Sep 2017 09:00:30 GMT

ok Peter,

there's a way to generate missing value based on distinct on the sales table?
Thanks

Re: Selection change data..

prieper — Wed, 13 Sep 2017 09:08:48 GMT

don't get your point