topic Re: How to get total difference in straight table against two values in a dimension? in QlikView

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

Anonymous — Wed, 28 Dec 2016 00:36:32 GMT

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.

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

Gysbert_Wassenaar — Wed, 28 Dec 2016 10:08:20 GMT

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

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

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

Anonymous — Wed, 28 Dec 2016 19:26:09 GMT

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.

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

johnw — Wed, 28 Dec 2016 19:38:08 GMT

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

,...)))

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

Anonymous — Wed, 28 Dec 2016 19:49:37 GMT

Good idea. Let me try that.

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

Anonymous — Wed, 28 Dec 2016 23:45:58 GMT

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)

)))

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

Anonymous — Thu, 29 Dec 2016 00:43:36 GMT

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

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

johnw — Thu, 29 Dec 2016 00:44:49 GMT

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.

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

johnw — Thu, 29 Dec 2016 00:48:21 GMT

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

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

johnw — Thu, 29 Dec 2016 01:04:09 GMT

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.

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

Anonymous — Sun, 08 Jan 2017 08:41:30 GMT

Hi John,

I tried your example and it works on simple sum. However, I have percentage calculation and I want to do the difference between the two week percentage. It doesn't work when I use the sum and sign from your example .

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

johnw — Mon, 09 Jan 2017 17:33:07 GMT

OK, back to the previous idea. I've added a Weeks Back field, and used this expression. This might not be exactly what you want, but the idea is to check the column to decide which calculation to perform.

if(Column='W/W',num(sum({<[Weeks Back]={1}>} CSAT)/sum({<[Weeks Back]={2}>} CSAT)-1,'+#,##0%'),num(sum(CSAT),'#,##0'))

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

Anonymous — Tue, 10 Jan 2017 01:40:27 GMT

Hi John,

You are getting close. However, I want the % calculate for each week and the do the different on the two weeks. See image and Qlikview file where I added another dimension to the raw data.

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

johnw — Tue, 10 Jan 2017 19:06:33 GMT

if(Column='W/W'
,sum({<SL={'SL1'},[Weeks Back]={1}>} CSAT)/sum({<[Weeks Back]={1}>} CSAT)
-sum({<SL={'SL1'},[Weeks Back]={2}>} CSAT)/sum({<[Weeks Back]={2}>} CSAT)
,sum({<SL={'SL1'}>} CSAT)/sum(CSAT))