topic Re: Using IF with set analysis in QlikView

Using IF with set analysis

Sun, 30 Sep 2012 15:02:34 GMT

Hello,

I have this expession:

If(year_DI = KPI.Year and month_DI = KPI.Month, Sum({$ <KPI_Reader.Year= , KPI_Reader.Month= > } KPI_Work.Sales))

This expression wait for user select and works.

But I need expression (no wait for user select):

Sum (If (year_DI = KPI.Year and month_DI = KPI.Month, {$ <KPI_Reader.Year= , KPI_Reader.Month= > } KPI_Work.Sales))

but this not work, the set analyse is problem in this syntax.

Thank you for any help!

Mirek

Re: Using IF with set analysis

sebastiandperei — Sun, 30 Sep 2012 15:28:33 GMT

why you say that the set part has an error?

Send a reduced copy please..

Re: Using IF with set analysis

Sun, 30 Sep 2012 15:48:12 GMT

Because first curly bracket is underlined and it's not working.

I cant send it because od sensitive data, sorry. But..

Sum (If (year_DI = KPI.Year and month_DI = KPI.Month, {$ <KPI_Reader.Year= , KPI_Reader.Month= > } KPI_Work.Sales))

(If (year_DI = KPI.Year and month_DI = KPI.Month, - This part is about data island and selecting year and month from correct dimension.

{$ <KPI_Reader.Year= , KPI_Reader.Month= > } - this part is about set analysis, which make calculation resistant from other selects over other date dimensions.

Mirek

Re: Using IF with set analysis

giakoum — Sun, 30 Sep 2012 15:57:18 GMT

put the sum in the if statement not outside :

If (year_DI = KPI.Year and month_DI = KPI.Month, sum({$ <KPI_Reader.Year= , KPI_Reader.Month= > } KPI_Work.Sales))

Re: Using IF with set analysis

Sun, 30 Sep 2012 16:19:02 GMT

That's what I have now.. But to start calculate, user must do select.

I need to calculate before select. Here is example:

Sum ( If ( Salesman='Helen', Sales)) - calculating Sales always

but

If (Salesman='Helen', Sum(Sales)) - waiting for user select

Re: Using IF with set analysis

giakoum — Sun, 30 Sep 2012 16:35:02 GMT

not sure what you mean actually but if you omit the if, user does not need to select :

sum({$<Salesman={"Helen"}>} Sales)

This will always sum sales for Helen even if another salesman is selected, and not Helen.

Not sure if this is what you need.

Re: Using IF with set analysis

jonathandienst — Sun, 30 Sep 2012 17:05:06 GMT

Is this what you need (I have just corrected the syntax on the second statement in your original post)?

Sum({<KPI_Reader.Year=, KPI_Reader.Month=>} If (year_DI = KPI.Year and month_DI = KPI.Month, KPI_Work.Sales))

Hope that helps

Jonathan

PS you could also put what's in the If statement into the set expression, but the syntax depends on whether the items are fields or variables.

Re: Using IF with set analysis

Thu, 04 Oct 2012 09:48:59 GMT

Hello,

I created the example of my problem. I have 2 different dates in two tables - Reader and Work.

In first table I count fact and to get right number I must select date (Year and Month) from this table (Reader).

In second table I count other two facts and to get correct number I must select date (Year and Month) from this table (Work).

I tried to use Data Island and use set analysis to make expressions resistant for other date dimension select.

Thank you for any help!