topic Re: Set expression problem in QlikView

Set expression problem

sarasunagaram — Fri, 06 Apr 2018 08:37:18 GMT

Hi All,

Set expression is not working properly ,it is giving zero values.

vperiod-SubField(PeriodWeek,'-',2)

Exp: sum({<Period={"$(vperiod)"}>}Sales)

Please find the attached app an data.

Thanks in Advance.

Muni

Re: Set expression problem

petter — Fri, 06 Apr 2018 09:03:46 GMT

To get something that works I believe you have to do this:

Sum( {<UniqueRowID={"=Period=SubField(PeriodWeek,'-',2)"}>} Sales)

The UniqueRowID is as the name implies a field that uniquely identifies each and every row in the table that contains the Sales field.

If you don't have a candidate for that in the table already you will have to create one. It is easy to do in your load script by creating a calculated field in the load statement of the table that contains the Sales field:

SALES:

LOAD

RowNo() AS Sales.uid,

Sales,

.....

If you do the expression would be changed to:

Sum( {<Sales.uid={"=Period=SubField(PeriodWeek,'-',2)"}>} Sales)

Re: Set expression problem

sarasunagaram — Fri, 06 Apr 2018 09:37:44 GMT

can we get same result with out new field

Re: Set expression problem

chinnuchinni — Fri, 06 Apr 2018 09:43:01 GMT

try this:

vperiod1 =mid(PeriodWeek,6,3)

and

expression as:

if(Match(mid(PeriodWeek,6,3),'03','06','09','12'

sum({<Period={"$(vperiod1)"}>}Sales)/5,

sum({<Period={"$(vperiod1)"}>}Sales)/4

)

Re: Set expression problem

petter — Fri, 06 Apr 2018 09:46:25 GMT

As I mentioned - if you already have a field in the table that contains the Sales field that is unique across the rows you can use that and you don't need a new field.

Look at the Excel data all the rows have the Period equal to the subfield of the PeriodWeek so I don't understand what you are trying to achieve with this set expression?

Re: Set expression problem

jaibau1993 — Fri, 06 Apr 2018 10:23:34 GMT

The set analysis expressions are evaluated once per chart so

{<Period={"$(vperiod)"}>}

does not work because you want a different value of the variable vperiod for each value of the dimension (PeriodWeek). Why dont you use an IF statement?

If( Match(Only({1<Week>}Right(SubField(PeriodWeek,'-',2),2)),'03','06','09','12'),

sum(if(Period=$(vperiod), Sales))/5,
sum(if(Period=$(vperiod), Sales))
/4
)

check the attached App.qwv if you prefer!

Bests,

Jaime.

Re: Set expression problem

sarasunagaram — Fri, 06 Apr 2018 10:34:52 GMT

i have some other filters in the set expression not only period.

Re: Set expression problem

jaibau1993 — Fri, 06 Apr 2018 10:39:38 GMT

You can keep that filters except the period and include the IF:

If( Match(Only({1<Week>}Right(SubField(PeriodWeek,'-',2),2)),'03','06','09','12'),

sum( {< OTHER FILTERS>} if(Period=$(vperiod), Sales))/5,
sum( {< OTHER FILTERS>} if(Period=$(vperiod), Sales))/4
)

Re: Set expression problem

sarasunagaram — Fri, 06 Apr 2018 11:41:47 GMT

it is not filter the data by above expression.can you please try some other exp.

Re: Set expression problem

boorgura — Fri, 06 Apr 2018 14:47:40 GMT

Please try this:

If(Match(((Only({1<Week>}Right(SubField(PeriodWeek,'-',2),2)))),'03','06','09','12'),

sum(if(Period=SubField(PeriodWeek,'-',2), Sales))/5,

sum(if(Period=SubField(PeriodWeek,'-',2), Sales))

)

Re: Set expression problem

sarasunagaram — Fri, 06 Apr 2018 16:08:32 GMT

Hi Rakesh ,

I have some other filters in my set expression.how can give those as well.

Re: Set expression problem

boorgura — Fri, 06 Apr 2018 17:08:20 GMT

if they are based on Period week, adjust the if condition accordingly. Else, you can combine the if with a set expression.

SUM({<set_expression>} if(condition, Sales)

SUM(if(condition_1 and condition_2, Sales)

Re: Set expression problem

sarasunagaram — Fri, 06 Apr 2018 17:41:16 GMT

This is the sum({$<Period ={"<=$(=Only({<Week,Month>}subfield(PeriodWeek,'-',2) ))"}>} Sales) expression using in my chart

Re: Set expression problem

boorgura — Fri, 06 Apr 2018 17:43:24 GMT

what exactly are you trying to achieve here?

Re: Set expression problem

sarasunagaram — Fri, 06 Apr 2018 17:46:14 GMT

here regardless of my week and month show data according to that row period. my dimension is periodweek. Problem is period is not able to compare with sub field value.

Re: Set expression problem

boorgura — Fri, 06 Apr 2018 17:57:51 GMT

please confirm - if this is what you want.

If period is 03,06,09 or 12 sum(Sales)/5 else sum(Sales)/4.

That's it? Or any other criteria?