Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have a task that in the beginning looked easier than it is.
I have a factsheet with price information of a certain date. I would like to highlight any change of Price and used the expression Before(Price). It works - but only if only one product is selected. If all products are shown in the table, it doesn't work.
Here are the examples:
1. This I would like to see as a table of all products:
As said, it works fine if only one product is selected.
One Product with Expression "Before(PriceNet) "
If all products are shown (=no selection) I get following result:
2. My Data Model Looks like this:
The xlsx-file with products and Price Information:
This list contains all products with all prices. Uploads (with repeats or changes) are done either once or twice per month. For this reason I use a daily calendar. That way I hoped to be able to Chose whatever period I would like to see.
Does any of you can explain why this works for one product selected but not for all?
What do I have to change withion the formula "before(NetPrice)" ? Any Set Analysis trick?
Many thanks
Frank
try this expression in your background:
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=0,white(),
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=sum(Price),white(), green()))
in your text:
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=0,black(),
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=sum(Price),black(),white()))
try this expression in your background:
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=0,white(),
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=sum(Price),white(), green()))
in your text:
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=0,black(),
if(aggr(nodistinct rangesum(above(sum(Price),1)), Product, Date)=sum(Price),black(),white()))
@f1234567 try below. You need to include full expression for PriceNet as highlighted
Before(total sum(PriceNet))
Many thanks ! I will check your suggestion ASAP ( I am away from my desk right now). I’ll let you know.
CU!
before(sum(PriceNet)) will work for straight table but not when pivoted
@edwin : thanks! That is correct. it does not work in pivot tables. (My table is pivoted)
Great! Works fine.
(although I do not really understand why this works 😞
Many thanks, Edwin!
Frank
Hi Kush,
I checked it out. It works, but only in non-pivot-tables. For pivot, Edwin’s solution works fine.
many thanks for your help!
Frank