Set Analysis Multiples If And Diferent Fields Operator +

[pgalvezt2021]

Hello !

I have this Formula. It works Fine aand show the correct result for example 400

Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}if(CodMC=1,[TOTAL FAC]))

But,

When I try to 2 or more conditions (If Sentence) Qlik Show incorrect results:

Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}if(CodMC=1,[Cant]*[$(vCost)])+[TOTAL FAC])

This formula must be containt 2 If sentence inside of the set analysis. Because by leaving out the IF's the formula only moves the first value and not all of them, since it is a set analysis that shows 12 moving months.

I hope you can find the answer I'm looking for.

Thank you !!!!!

[rubenmarin]

Hi, wich part is failing? maybe is the $(vCost), note that the set analysis might not affect the $(vCost), it depends on what it contains.

[pgalvezt2021]

Hello !

Thank you for your reply,

This fails when I add [TOTAL FAC] to this [Qty]*[$(vCost)]) : This part works fine without the [TOTAL FAC].

[TOTAL FAC] It also works fine if I remove the above. But when I put them together in a sum ([Qty]*[$(vCost)])+[TOTAL FAC])). It does not work. What I really want is to know how to add within the set analysis because if I put the multiplier and divisor operator they work fine but when I put the + sign Qlik is not really doing the sum for me.

[rubenmarin]

Hi, [TOTAL FAC] is a field name? and that field data is in the same rows than the others? You can try with Alt in each part, so if it's null, it returns zero:

Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}Alt(if(CodMC=1,[Cant]*[$(vCost)]),0)+Alt([TOTAL FAC],0))

[pgalvezt2021]

Hello,

[TOTAL FAC] is a field name?

Yes [TOTAL FAC] : It is from a different table

and that field data is in the same rows than the others?

No, It is from a different table.

Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}Alt(if(CodMC=1,[Cant]*[$(vCost)]),0)+Alt([TOTAL FAC],0))

This formula shows result. but the amounts are too exuberant.

[pgalvezt2021]

Hi,

What I want is to make this work within a set analysis where it shows me 12 rolling months:

Sum([Cant]*[$(vCost)])+Sum([TOTAL FAC]) : This works perfectly and shows me the correct data. but obviously it does it for 1 month. I want to do it for 12 rolling months.

Thanks !

[rubenmarin]

Hi, you can make a test adding a table with Fecha as dimension and 2 expressions:

- Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}if(CodMC=1,[Cant]*[$(vCost)]))

- Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}[TOTAL FAC])

And check the values of each expression separatedly to try to isolate wich one causes the wrong result, maybe [TOTAL FAC] has a duplicated value for each invoice line so the total is multiplied bu the number of rows.

Or if it works separatedly just sum both expressions: Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}if(CodMC=1,[Cant]*[$(vCost)])) + Sum({<Fecha= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Año]=, Mes=>}[TOTAL FAC])

[pgalvezt2021 · Accepted solution]

Hello,

There is no way to incorporate that field ([TOTAL FAC]) into this: ,[Qty]*[$(vCost)])), Something like ([Qty]*[$(vCost)])+[TOTAL FAC].
What happens is that I have a filter where the user has to choose if they want to see amounts or quantity and have all the graphs move.

and although this works fine:

Sum({<Date= {">=$(vDateStartMax11)<=$(vDateEndMax)"},[Year]=, Month=>}if(MCCod=1,[Qty]*[$(vCost)])) + Sum({<Date= {">=$(vFechaStartMax11)<=$(vFechaEndMax)"},[Year]=, Month=>}[TOTAL FAC])....

And that I already performed tests previously. By adding the other If to show the amount. It doesn't work. because only the current month is changed and not the 12 months. Something happens to the set analysis with 12 rolling months when you create an Inline table with 2 Values ​​1 and 2. You use those values ​​so that you can choose with if if you want to see the amount or the quantity. With 1 month there is no problem.

Thanks !

[pgalvezt2021]

Here is the final solution
Num(
Num(Sum({<Date= {">=$(vDateStartMax11)<=$(vDateEndMax)"},Year,Month>}If(MCCod= 1,[Qty]*[$(vCost)],[Qty] )/vdivider),'$(vNumFormat)')+
Num(Sum({<Date= {">=$(vDateStartMax11)<=$(vDateEndMax)"},Year,Month>}If(MCCod= 1,Tot,QTY)/vdivisor),'$(vFormatNum)' ),'$(vNumFormat)').

You really helped me find the solution, thank you very much!!!!!