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: 
c_pannese
Partner - Creator
Partner - Creator

Above function in If expression on Dates

HI all,

my table is:

qlik3.png

the test column is:

if(COlumn(1)>0,
If(Above(TOTAL [Cod.Articolo])=[Cod.Articolo],
above(total (today()-1+ceil(Column(3))))+ceil(Column(3)),
today()-1+ceil(Column(3))))

But it doesn't work. Any ideas?

Tahnk you all

17 Replies
c_pannese
Partner - Creator
Partner - Creator
Author

i'm trying but i'm working on a server and i have to reduce the data a lot..

c_pannese
Partner - Creator
Partner - Creator
Author

i've done an extaction but in the qvf your formula works correctly. At this point I think that the problems are the column before that I can't reproduce in a sample qvf.

Giacenza al :

num(sum({$<[%Tp_dato]={7}, [%Data]={"$(=date(today()-1,'DD/MM/YYYY'))"},
dt_scad_art={">=$(=min(Data_confronto2)) <=$(=max(Data_confronto2))"}>} #qnt ),'#.##0')

and Consumo medio:

if(column(1)>0,
sum(total<[%Magazzino],[%Articolo]>aggr (
if(segno=1 or isnull(segno) or (segno='-1' and len(trim([%Reso]))>0),
rangesum(
sum({$<#um={'PL'},PCU_0={'PL'},Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1},cod_lotto=>}#qnt*PCUSTUCOE_0)*segno,
sum({$<#um={'PL'},PCU_0={'PL'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'},cod_lotto=>}#qnt*PCUSTUCOE_0),
sum({$<#um={'PL'},PCU_1={'PL'},Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1},cod_lotto=>}#qnt*PCUSTUCOE_1)*segno,
sum({$<#um={'PL'},PCU_1={'PL'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'} ,cod_lotto=>}#qnt*PCUSTUCOE_1),
sum({$<#um={'PL'},PCU_2={'PL'},Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1},cod_lotto=>}#qnt*PCUSTUCOE_2)*segno,
sum({$<#um={'PL'},PCU_2={'PL'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'} ,cod_lotto=>}#qnt*PCUSTUCOE_2),
sum({$<#um={'KG'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1}, cod_lotto=>}#qnt)*segno,
sum({$<#um={'KG'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'} ,cod_lotto=>}#qnt),
sum({$<#um={'PZ'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1}, segno={1}, cod_lotto=>}#qnt)*segno,
sum({$<#um={'PZ'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'} ,cod_lotto=>}#qnt),
sum({$<#um={'CT'},unita_mag_art={'PZ'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1}, cod_lotto=>}#qnt*pezzi_cart_kg_agan)*segno,
sum({$<#um={'CT'},unita_mag_art={'PZ'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'} ,cod_lotto=>}#qnt*pezzi_cart_kg_agan),
sum({$<#um={'CT'},unita_mag_art={'CT'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={1}, segno={1}, cod_lotto=>}#qnt)*segno
,sum({$<#um={'CT'},unita_mag_art={'CT'},%Data={">=$(=min(Data_confronto)) <=$(=max(Data_confronto))"},[%Tp_dato]={3},%Fattura={'nd'},%Cliente-={'F00455','C13487','F00457','C23919','C25122'} ,cod_lotto=>}#qnt)
)
),[%Magazzino],[%Articolo],[%Fattura]))
/
count(distinct Data_confronto))

 

lorenzoconforti
Specialist II
Specialist II

"i've done an extaction but in the qvf your formula works correctly." - are you saying that the formula I sent actually works and fixes the problem?

"At this point I think that the problems are the column before that I can't reproduce in a sample qvf." - maybe just send to excel a large table from your online dashboard (not just 3 rows like in the example you already sent). Then load it inline in a new app so we can concentrate on the formula

 

c_pannese
Partner - Creator
Partner - Creator
Author

yes, your formula give the correct result but in my table doesn't work.

i did the real extraction in this qvf with all measures.

thank you!

c_pannese
Partner - Creator
Partner - Creator
Author

sorry, i realized that is necessary insert a fixed date in column "Giacenze al". Thisi is the new.

lorenzoconforti
Specialist II
Specialist II

Hi, I went over this. I don't think it's possible. For two reasons:

1 - Above doesn't work on dimensions, only on measures. There is a workaround for this: to create a measure with the function only()

2 - In the measure "Consumo medio" you have quite a complex expression.  You have an aggregation with sum range nested on dimensions you don't have on your main table. The effect of this is that such expression returns a number of empty rows which are hidden by un-ticking "Include zero values" under Data Handling. Visually it is fine; the problem is that those hidden rows are still evaluated by the Above function; the result of this is that, even if you implement the workaround as per point 1, you are still stuck with these extra rows. 

The only possible solution (but I'm not sure if it will work either) is to include in your loading script as much as possible of the calculations included in Consumo medio; I can see that most calculations and set expression elements are fixed so you should be able to bring them into your data structure. There will still be the challenge with the aggregation but I don't know how your data is structured and the workings of the various measures

Lorenzo

c_pannese
Partner - Creator
Partner - Creator
Author

Hi Lorenzo,

I've chsnged two dimensions in the table and now it seems work! 

You grave me a great help! 

Thank you very much!

lorenzoconforti
Specialist II
Specialist II

Would you mind sharing the dashboard with the changes? I'm curious to see how you've fixed it