topic Re: Above function in If expression on Dates in App Development

Above function in If expression on Dates

c_pannese — Sat, 16 Nov 2024 02:53:18 GMT

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

Re: Above function in If expression on Dates

lorenzoconforti — Wed, 25 Mar 2020 17:37:06 GMT

What outcome are you trying to achieve?

this calculation basically says: take today's date and remove one day, then add "Data fine scorte" from the row above and then add "Data fine scorte" from the current row. Unless I got the columns wrong, you are adding 3 times a date

above( total ( today() - 1 +ceil(Column(3) ) ) ) + ceil(Column(3))

Re: Above function in If expression on Dates

c_pannese — Wed, 25 Mar 2020 18:08:05 GMT

i have two cases:

if the article appears only once or on the first row i need "yesterday+Durata (column 3)"

when article apeeras more than once i need " the above date +Durata" starting the second row for those article.

articolo	data scadenza	durata (gg)	data fine scorte
A	31/03/2020	10	10/04/2020
B	3/04/2020	30	03/05/2020
B	1/05/2020	5	08/05/2020

Durata cames from other calculations

thanks

Re: Above function in If expression on Dates

lorenzoconforti — Wed, 25 Mar 2020 18:20:22 GMT

In your original formula:

- which field is Column(1)? "Unita' di ma.."?

- why are you adding "ceil(Column(3))" twice?

Re: Above function in If expression on Dates

c_pannese — Wed, 25 Mar 2020 19:45:19 GMT

which field is Column(1)? "Unita' di ma.."?

column(1) is "Giacenze Al"

"Unità di Magazzino" is a dimension

"Consumo Medio" is column(2)

Durata is column(3)

why are you adding "ceil(Column(3))" twice?

Because the first time i should calculate the starting date and the second time for adding new days. Isn't it?

- why are you adding "ceil(Column(3))" twice?

Re: Above function in If expression on Dates

c_pannese — Wed, 25 Mar 2020 20:08:29 GMT

Maybe it's more correct calculate the cumulative of the field "Durata" for each article?

thanks

Re: Above function in If expression on Dates

lorenzoconforti — Wed, 25 Mar 2020 20:10:52 GMT

According to your previous post:

- 1st condition: "yesterday+Durata (column 3)" =today() - 1 + Column(3)

- 2nd condition: " the above date +Durata" =above(TOTAL [Data scadenza]) + Column(3) [in this case, do you need the above Column(3) or the one from the same row? Also, which one is the above date? Data Scadenza?]

Re: Above function in If expression on Dates

lorenzoconforti — Wed, 25 Mar 2020 20:11:39 GMT

Can you share the QVF and an Excel file where you show exactly what you are looking for?

Re: Above function in If expression on Dates

c_pannese — Wed, 25 Mar 2020 21:35:18 GMT

this is what i'm looking for

Re: Above function in If expression on Dates

lorenzoconforti — Wed, 25 Mar 2020 22:18:03 GMT

This would go in Data Fine Scorte in your table:

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

Can you share the qvf?

Re: Above function in If expression on Dates

c_pannese — Wed, 25 Mar 2020 22:26:06 GMT

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

Re: Above function in If expression on Dates

c_pannese — Thu, 26 Mar 2020 06:32:56 GMT

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

Re: Above function in If expression on Dates

lorenzoconforti — Thu, 26 Mar 2020 08:54:05 GMT

"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

Re: Above function in If expression on Dates

c_pannese — Thu, 26 Mar 2020 21:34:42 GMT

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!

Re: Above function in If expression on Dates

c_pannese — Fri, 27 Mar 2020 12:00:10 GMT

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

Re: Above function in If expression on Dates

lorenzoconforti — Fri, 27 Mar 2020 22:55:51 GMT

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

Re: Above function in If expression on Dates

c_pannese — Sun, 29 Mar 2020 15:40:19 GMT

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!

Re: Above function in If expression on Dates

lorenzoconforti — Mon, 30 Mar 2020 10:35:48 GMT

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