the same value in different fields

[beck_bakytbek]

Hi Folks,

i am facing one issue, my data do look like:

project, invoicedate, paiddate, amount

a, 01.05.2021, 06.06.2021, 5

b, 05.06.2020, 08.08.2020 6

c, 08.07.2020,  09.03.2021. 10

my issue is, is that possible the following selection: if i select the invoicedate = 2021, then i want to see in field: paiddate only the year = 2021 and amount= 15. i tried to rectify this issue by using the set expression, but i didn't achieve any results.

Does anybody have any idea?

Thanks a lot

Beck

[Chanty4u]

Hi,

which expression you used?

create  Year(invoicedate)  as In_Year

take stright table   use dimension 

In_year   

measure : sum(amount) .

[beck_bakytbek]

Hi Chanty4u,

first of all thanks a lot for your time and reply, my issue is, if i select in one column (for instanct) year 2021, then i want to see in another column the same year 2021, 

for example:

invoicedate  > Year= 2021 when i still in a row = thean i have amount = 5, but i want to see tha amount: 15 (because both values are from year 2021), i am using this expression: 

if( sum( {< Invoiceyear = {"$(=Max(Invoiceyear ))"} >} amount, sum( {< PaidYear= {"$(=Max(PaidYear))"} >} amount,

sum(amount))

i hope i explained enough well, let me know if you need more information.

thanks a lot for your time and help

Beck

[menta]

You can use

sum( {< PaidYear= {"$(=Max(InvoiceYear))"} >} amount) and select 2021 in InvoiceYear.

[beck_bakytbek]

Hi Menta, thanks a lot for your reply, i tried it, but it doesn't work (not dynamicaly), what happens, if i select the year 2020 (not max),  unfortunately it does not work

[menta]

If you select 2020 also paid = 2020

If you always want the max paidyear you can use this:

sum( {< PaidYear= {"$(=Max(PaidYear))"},InvoicedYear= >} amount)

[beck_bakytbek]

Hi Menta  thanks a lot for your feedback,

my expected output is: if invoicedate = 2020 i want to have paiddate = 2020, if i select invoicedate = 2021 i want to see the paiddate = 2021

[menta]

This formula works:

sum( {< PaidYear= {"$(=Max(InvoiceYear))"} >} amount) and select 2021 in InvoiceYear

if you select 2021 in invoiceyear became:

Sum({<PaidYear= {2021"} >} amount)

if you select 2020 in invoiceyear

Sum({<PaidYear= {2020"} >} amount).

Attention:

Try this on a kpi without dimension

[beck_bakytbek]

Hello Menta, thanks a lot for your reply and help, i wanted to create one expression, which i can use within pivot, your suggestion does work great, but not dynamicaly

"my expected output is: if invoicedate = 2020 i want to have paiddate = 2020, if i select invoicedate = 2021 i want to see the paiddate = 2021"

thanks a lot for your help