# New to QlikView

Discussion board where members can get started with QlikView.

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 Code SalesRepCode Qty Total Qty if(\$(v_Qty)=0,(\$(v_Qty_Total)/\$(v_NbSalesmen)),\$(v_Qty)) 270 270 270 1 PO 52 148 52 1 PR 96 148 96 1 PT 0 148 49,33 2 PO 33 122 33 2 PR 75 122 75 2 PT 14 122 14

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.

Loreen

8 Replies
MVP

## 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]))`
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°]))

New Contributor III

## Re: Replace or change value in straight table

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° Salesman Qty Total Qty if(\$(v_Qty)=0,(\$(v_Qty_Total)/\$(v_NbSalesmen)),\$(v_Qty_M3C)) New total qty 1 PO 52 148 52 197,3333333 2 PO 33 122 33 122

 Product n° Salesman Qty Total Qty if(\$(v_Qty)=0,(\$(v_Qty_Total)/\$(v_NbSalesmen)),\$(v_Qty_M3C)) New total qty 1 PO 52 148 52 52 2 PO 33 122 33 33
New Contributor III

## Re: Replace or change value in straight table

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

MVP

## 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)

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.

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

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.