Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Loreen
Contributor III
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
sunny_talwar

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

@jolivares 

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

sunny_talwar

How do you define these variables

$(v_Qty_M3_LY)

$(v_Qty_Totalparproduit_LY)

$(v_Nb_ToutVendeurs)

jolivares
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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.