Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
which expression you used?
create Year(invoicedate) as In_Year
take stright table use dimension
In_year
measure : sum(amount) .
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
You can use
sum( {< PaidYear= {"$(=Max(InvoiceYear))"} >} amount) and select 2021 in InvoiceYear.
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
If you select 2020 also paid = 2020
If you always want the max paidyear you can use this:
sum( {< PaidYear= {"$(=Max(PaidYear))"},InvoicedYear= >} amount)
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
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
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