Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear value community,
I have a problem on the visulization of a data in a simple chart.
I want to display as dimension only the customer with an Invoice in the last 7 days, I made this formula as calculated dimension:
=if([Invoice Date]>Today()-7, [Customer n])
As Expression, I want to see the Invoice value of total year 2019.
sum({$<[Invoice year]={'2019'}>} [Invoice value] )
The problem that occur is that the expression display only the Invoice value of the last 7 days, not of the total year.
How can I fix the formulas?
Many thanks
Andrea
Hi Andrea,
if I understood correctly: you want to display for each customer: the sum of the [Invoice value] for the year 2019
so for dimension: OK
=if([Invoice Date]>Today()-7, [customer])
for Expression I propose :
=sum(aggr(sum({$<[Invoice year]={'2019'}>} [Invoice value]),customer) )
I did the test on the data source:
LOAD * INLINE [
customer, Invoice Date,Invoice year, Invoice value
1, 01/12/2018,2018, 100
2, 31/10/2019,2019 ,200
3, 27/11/2019,2019, 300
4, 28/11/2019,2019, 400
4, 29/11/2019,2019, 250
4, 29/11/2018,2018, 150
];
and the results:
Regards,
Hi,
take the Dimension as Customer
and u can add your calculated expression into Set analysis
i mean [Invoice Date]>Today()-7 add this also into your set analysis.
Move the customer selection from the dimension to the set expression, Something like this:
sum({$<
[Invoice year] = {'2019'},
[Customer n] = P({<[Invoice Date] = {"$(=Date(Today() - 7))"}>} [Customer n])
>} [Invoice value])
Hi Andrea,
if I understood correctly: you want to display for each customer: the sum of the [Invoice value] for the year 2019
so for dimension: OK
=if([Invoice Date]>Today()-7, [customer])
for Expression I propose :
=sum(aggr(sum({$<[Invoice year]={'2019'}>} [Invoice value]),customer) )
I did the test on the data source:
LOAD * INLINE [
customer, Invoice Date,Invoice year, Invoice value
1, 01/12/2018,2018, 100
2, 31/10/2019,2019 ,200
3, 27/11/2019,2019, 300
4, 28/11/2019,2019, 400
4, 29/11/2019,2019, 250
4, 29/11/2018,2018, 150
];
and the results:
Regards,
Thank you Taoufiq ,
You understood correctly. Your answer is solved my problem.
Best regards
Andrea