Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Loreen
New Contributor III

Replace or change value in straight table

Hi everybody,

In a straight table, I'd like to replace the value of a field when the value is 0.

Here is the expression that I created :

=if($(v_Qty)=0,($(v_Total_Qty)/$(v_NbSalesmen),$(v_Qty))

This is what I get in my straight table :

Material CodeSalesRepCodeQtyTotal Qtyif($(v_Qty)=0,($(v_Qty_Total)/$(v_NbSalesmen)),$(v_Qty))
  270270270
1PO5214852
1PR9614896
1PT014849,33
2PO3312233
2PR7512275
2PT1412214

 

The problem is that the substitute value ( here it's 49.33) is not taken into account in the total of the new expression. Instead of 270, I should have 319,33. I know that I can choose the option "Sum of rows". With it, I do have the good result.

My problem is that I need to calculate a new Total quantity with this expression, so this solution does not solve my problem.

I also tried : if($(v_Qty)=0,$(v_Qty)+($(v_Qty_Total)/$(v_NbSalesmen)),$(v_Qty)) but the result is the same

For my new Total Qty, the forum helped me find an expression that works

Sum(TOTAL<Material_Code>Aggr(if($(v_Qty_M3_LY)=0,($(v_Qty_Totalparproduit_LY)/$(v_Nb_ToutVendeurs)),$(v_Qty_M3_LY)),[Material_Code],[SalesRep_Code]))

But I need to fix the result of this, whatever the Sales Representant I choose to filter.

You can check the result that I'm looking for in the attached excel file.

Thanks in advance for your help

Loreen

8 Replies

Re: Replace or change value in straight table

So, are you looking to get 'New total qty' column from the Excel file and this didn't do it?

Sum(TOTAL<Material_Code>Aggr(if($(v_Qty_M3_LY)=0,($(v_Qty_Totalparproduit_LY)/$(v_Nb_ToutVendeurs)),$(v_Qty_M3_LY)),[Material_Code],[SalesRep_Code]))
jolivares
Valued Contributor

Re: Replace or change value in straight table

Hi Loreen.

Expressions for:

Total_Qty = Sum(distinct Aggr(nodistinct Sum([Qty]),[Product n°]))

Total_Qty1 = Sum(if(Qty=0,(Aggr(nodistinct Sum([Qty]),[Product n°]))/3,Qty))

New_Total = Sum(distinct Aggr(nodistinct Sum(if(Qty=0,(Aggr(nodistinct Sum([Qty]),[Product n°]))/3,Qty)),[Product n°]))

Loreen
New Contributor III

Re: Replace or change value in straight table

Thanks for your reply,

Yes that expression works. But i'd like that  results do not change when I filter the Sales Representant.

I'd like to have this :

Product n°SalesmanQtyTotal Qtyif($(v_Qty)=0,($(v_Qty_Total)/$(v_NbSalesmen)),$(v_Qty_M3C))New total qty
1PO5214852197,3333333
2PO3312233122

 

But instead I have

Product n°SalesmanQtyTotal Qtyif($(v_Qty)=0,($(v_Qty_Total)/$(v_NbSalesmen)),$(v_Qty_M3C))New total qty
1PO521485252
2PO331223333
Loreen
New Contributor III

Re: Replace or change value in straight table

@jolivares 

Thanks for your reply. My Qty field is an expression, not a dimension so I can't make a sum with it.

Re: Replace or change value in straight table

How do you define these variables

$(v_Qty_M3_LY)

$(v_Qty_Totalparproduit_LY)

$(v_Nb_ToutVendeurs)

jolivares
Valued Contributor

Re: Replace or change value in straight table

But you have a field with this data right? so you can work with it, even in that table it is an expression.  I just took your data from the excel file.  Anyway you can take the expression I sent to you and modify them.

 

Loreen
New Contributor III

Re: Replace or change value in straight table

My analysis focuses on 17 salesmen ( salesmen = vendeurs)

$(v_Nb_Toutvendeurs) = 17

I created an expression that gives me the quantity sold but only those 17 salesmen : $(v_Qty_17vendeurs) 

$(v_Qty_M3C_Y_LY) = this is the quantity of the last year

$(v_Qty_Totalparproduit_LY) = Sum(Aggr(NODISTINCT($(v_Qty_17vendeurs_Y_LY)),Material_Code))

$(v_Qty_M3_LY) = if($(v_Qty_M3C_Y_LY)=0,($(v_Qty_Totalparproduit_LY)/$(v_Nb_Toutvendeurs)),$(v_Qty_M3C_Y_LY))

 

jolivares
Valued Contributor

Re: Replace or change value in straight table

Its difficult to give you a solution with this variables in the middle of expression. I suggest that you try to put your code directly inside the expression in the table and then convert back to variable.

In addition you want to that the result remain as you select some filters. in this case it is necessary to use set analysis.