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: 
Anonymous
Not applicable

How to get total difference in straight table against two values in a dimension?

I have a bunch of metrics (left in image below) that I create as expressions and one date dimension (current and previous week) which limits only two dates. How do I get a total/difference column at the end to calculate the difference between the two weeks which is based on a dimension. Column WW is what I want.Untitled.png



13 Replies
Gysbert_Wassenaar

You can try using the column numbers: Column(3) - Column(2)

Or the expression labels: [Week 8] - [Week 7]


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Since Week 7 and Week 8 is two values in a dimension, I can't add another column after it to do the calculation. The column on the left where you see "SL1/Top25", "All" and "Inflow" are actual column expression in the straight table.  Week is a dimension that has value Week 7 and Week 8.  I just transposed the column to row.

johnw
Champion III
Champion III

Seems like you're trying to be like Excel and have a different expression for every single cell. That's technically possible, but not something QlikView is good at. In this case, you might add 'W/W' as a dimension value as well, and then pick the correct calculation based on the dimension value, like:

if(Dimension='Goal',goal calculation

,if(left(Dimension,4)='Week',weekly calculation

,if(Dimension='W/W',expression with set analysis to subtract one week from another

,...)))

Anonymous
Not applicable
Author

Good idea.  Let me try that.

Anonymous
Not applicable
Author

Hi,

I tried this if logic but the difference column is not coming up.

=

if(Week_Cal_Flag='Previous Week - 1',sum({<Week_Cal_Flag={'Previous Week - 1'}>}CSAT),

if(Week_Cal_Flag='Previous Week',sum({<Week_Cal_Flag={'Previous Week'}>}CSAT),

if(wildmatch(Week_Cal_Flag,'Previous*')>0,sum({<Week_Cal_Flag={'Previous Week'}>}CSAT) - sum({<Week_Cal_Flag={'Previous Week - 1'}>}CSAT)

)))Untitled.png

Anonymous
Not applicable
Author

Attached qvw is a sample data where I am playing with the expression calculation.  It doesn't seem to show the difference column.

Untitled.png

johnw
Champion III
Champion III

If your dimension is, say, just [Week Beg Date], and you have 12/11/2016 and 12/18/2016 selected, then that's it, you have two columns. To add another column, you need another value. And you probably don't want to corrupt the [Week Beg Date] field, so you'd want some new dimension linked to it, perhaps.

Wait, we can do better. Something like this.

NewField, Week Beg Date, Sign
12/11/2016, 12/11/2016, +1
12/18/2016, 12/18/2016, +1
W/W, 12/11/2016, -1
W/W, 12/18/2016, +1

Then a much simpler expression will work.

Dimension  = NewField
Expression = sum(CSAT * Sign)

But the exact table you need and exact values you need are going to depend a lot on your data model and how your user is going to be selecting values, or how else you're determining which two weeks to show. The idea, though, is to handle the complexity with data to the extent possible.

johnw
Champion III
Champion III

OK, I can get this working. I'll be back shortly.

johnw
Champion III
Champion III

OK, here's an example of what I'm talking about. I changed all the script to match up to my suggested new table and expression, but I think it's essentially the same data model, just with that extra bit controlling the chart.