Show excluded and not excluded values

[chematos]

Hi,

I have one problem to solve and this is the case:

I have to show a table with 3 dimensions and 4 expressions, doesn´t matter the calculations, but the user selects one Stock Date that it´s not showing at the table, it´s only used as filter, so the table shows information about data that has relation with that Stock Date but I need to show also the excluded data that has no realtion with that Stock Date beacuse one expression returns the same value having relation or not with Stock Date and users wants to see all at the same time.

I was trying to use functions p() and e() but this only works as far as I know if I use the same dimension that I´m selecting and only with one simple expression, something like this:

Dimension in tha table: Stock Date

Actual Selection: Stock Date

expression: sum({ 1-$<[Stock Date] = p([Stock Date]) >} AnyValue)

In my table, [Stock Date] is not a dimension, I have other dimensions and I use set analysis in the expressions because I´m calculating values since 12 months before the [Stock Date] selected.

Any help will be apreciated.

Regards,

Chema

[whiteline]

You can use any dimension in set analysis weather it's used in a chart or not.

Suppose you have some complex expression: SUM({$<Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

If you want to calculate the same expression over the excluded data you can use this:

SUM({1-$<Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

But if you want to invert your selection with the same logic you should keep all other conditions:

SUM({1<Field1={"Condition1"}, Field2={"Condition2"},...>-$<Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

Finally, if you want to invert only one field [Stock Date] and keep all other conditions you should use function e().

SUM({$<[Stock Date] = e([Stock Date]), Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

[chematos]

Hi whiteline,

This is my expression, I've tried your solution but still not working

aggr(

          NODISTINCT sum({<[Fecha_Stock] = e([Fecha_Stock]),

          Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0))

          ,[ROT.Mes Año]=,ROT.Mes=,ROT.Año=,Centro_Rotacion=,ROT.Fecha=>} [Cantidad_real]),           Componente_STOCK

        )

/

1000

Have you ever used the expression? Am I missing something?

Thanks

[whiteline]

Lets consider only your Sum expression:

sum({<[Fecha_Stock] = e([Fecha_Stock]),

          Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

          [ROT.Mes Año]=,

          ROT.Mes=,

          ROT.Año=,

          Centro_Rotacion=,

          ROT.Fecha=>} [Cantidad_real])

Suppose the user selected some [Stock Date].

If you want to calculate exactly that sum over all other values of [Stock Date], try this.

sum({<[Fecha_Stock] = e([Fecha_Stock]),

          Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

          [ROT.Mes Año]=,

          ROT.Mes=,

          ROT.Año=,

          Centro_Rotacion=,

          ROT.Fecha=,

          [Stock Date]=e([Stock Date])>} [Cantidad_real])

[chematos]

Sorry, [Stock Date] is [Fecha Stock] in my expression, I traduced the field at the beginning.

The case is this:

I have only one [Fecha Rotacion] in a table that has only information about stock of that day. This table has relation with other table that has more information, so I join [Fecha Rotacion] with this table renaming as [Fecha Stock], so I have [Fecha Stock]= [Fecha Rotacion] and [Fecha Stock]= null for all the components that doesn't have stock at that day.

In a straight table I have all I need but only with components that have stock at [ [Fecha Stock] | [Fecha Rotacion] ],

and as you know, I want the same but with components that have no stock at that day.

I don´t know if the explanation helps, but this is it.

Regards

[whiteline]

Ok. Now I understand.

You want not to just  invert selection but to include all data that has [Fecha Stock]=null.

e([Fecha Stock]) function inverse it like ListBox, so that you skip all [Fecha Stock]=null.

So you should use second solution from my first post.

First constract set with all [Fecha Stock] and then subtract the set with selected [Fecha Stock].

sum({1<Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

                   [ROT.Mes Año]=,

                   ROT.Mes=,

                   ROT.Año=,

                   Centro_Rotacion=,

                   ROT.Fecha=>

        -$<[Fecha_Stock] = e([Fecha_Stock]),

                   Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

                   [ROT.Mes Año]=,

                   ROT.Mes=,

                   ROT.Año=,

                   Centro_Rotacion=,

                   ROT.Fecha=>} [Cantidad_real])

[chematos]

Nothing happens.

The problem is when I select a [Fecha Stock], all my expressions will be about possible components so I can´t show excluded components.

I'm able to make calculations with excluded data but I can´t show excluded dimensions data.

I'm screwed!!

[whiteline]

Dimensions qlikview will show  if your expression has a value.

Play with the attached file.

[chematos]

If you select FullDate

Sum ({$<Date=e(Date)>}Value) returns nothing.

So is like I was saying in my first post:

"

I was trying to use functions p() and e() but this only works as far as I know if I use the same dimension that I´m selecting and only with one simple expression, something like this:

Dimension in tha table: Stock Date

Actual Selection: Stock Date

expression: sum({ 1-$<[Stock Date] = p([Stock Date]) >} AnyValue)

"

In the attached file is the same but with Date field instead of Stock Date

[#10]

I have not really been following this discussion, but I do remember something that might help you. I was reading somewhere that said that in set if you put ={} you will return only the nulls for that field.

So I would assume that putting [Fecha Stock]={} somewhere in your set will show you the null [Fecha Stock] values.

Update:

Found it:

Empty element sets, either explicitly e.g. <Product = {}> or implicitly e.g. <Product = {"Perpetuum

Mobile"}> (a search with no hits) mean no product, i.e. they will result in a set of records that are not associated

with any product. Note that this set cannot be achieved through usual selections, unless a selection is

made in another field, e.g. TransactionID.