Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
f1234567
Contributor III
Contributor III

Highlight Changes in a dimension of a Pivot Table

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:
Target.JPG
As said, it works fine if only one product is selected.

One Product with Expression "Before(PriceNet) "
OneProduct.JPG

If all products are shown (=no selection) I get following result:
AllProducts.JPG


2. My Data Model Looks like this:

DataModel.JPG

The xlsx-file with products and Price Information:
xlsx.JPG
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




1 Solution

Accepted Solutions
edwin
Master II
Master II

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

 

View solution in original post

7 Replies
edwin
Master II
Master II

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

 

Kushal_Chawda

@f1234567  try below. You need to include full expression for PriceNet as highlighted

Before(total sum(PriceNet))

f1234567
Contributor III
Contributor III
Author

Many thanks ! I will check your suggestion ASAP ( I am away from my desk right now). I’ll let you know.

CU!

edwin
Master II
Master II

before(sum(PriceNet)) will work for straight table but not when pivoted

f1234567
Contributor III
Contributor III
Author

@edwin : thanks! That is correct. it does not work in pivot tables. (My table is pivoted)

f1234567
Contributor III
Contributor III
Author

Great! Works fine.

(although I do not really understand why this works 😞  

Many thanks, Edwin!

Frank 

f1234567
Contributor III
Contributor III
Author

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