Above function in If expression on Dates

[c_pannese]

HI all,

my table is:

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

[c_pannese]

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

[c_pannese]

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]

"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]

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]

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

[lorenzoconforti · Accepted solution]

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]

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]

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