topic Re: Show intersection of row and column data in QlikView

Show intersection of row and column data

qliknexus_kgcru — Tue, 24 Jul 2018 02:06:50 GMT

Hi All,

I have a table below to show in Qlikview:

I have a Transaction Month which displays all the months in a Fiscal Year; a pick dimension which displays TM-0, TM-1, TM-2; a Date_ID; and a measure for Actual and Forecast.

For the Actual Measure, i have manage to show the actual value based on the most current Date_ID (20180101, I have highlighted the values for the Actual and Zero out the other values).

I need to achieve this for the Forecast measure, e.g. for TM-0, if the Transaction Month is OCT then it will take the intersection of the Transaction Month (OCT) and the Date_ID (20171001) to get the value. and if the Transaction Month is NOV then it will take the intersection of the Transaction Month (NOV) and the Date_ID (20171101)

for the TM-1, if the Transaction Month is NOV then it will take the intersection of the Transaction Month (NOV) and the Date_ID (20171001) to get the value. and if the Transaction Month is DEC then it will take the intersection of the Transaction Month (DEC) and the Date_ID (20171101) to get the value.

I also need to add a calculated quarter total, sum of Actual(OCT, NOV, DEC)/ sum of Forecast(OCT, NOV, DEC)

for the actual measure, i used a set analysis e.g.

Sum({1<Transaction Date = {"$(='>=' & date(monthstart(date#(Transaction Date,'YYYYMM'),-5),'YYYYMM') & '<=' & date(monthstart(date#(Transaction Date,'YYYYMM'),6),'YYYYMM'))"},Fiscal Year, Fiscal Month, Fiscal Quarter , Date_ID= {'$(vpsi_latest_TID)'}>}#PSI_AMOUNT)

vpsi_latest_TID is a variable that displays the latest date_id

would appreciate any help. thanks!

stalwar1‌

Re: Show intersection of row and column data

sunny_talwar — Tue, 24 Jul 2018 11:42:48 GMT

What is the expression for FORECAST (since that is the one we need to work on). Also, how do you create Date_ID and Transaction Month in the script? Are they created using Date and Month functions in the script, respectively? Would it be possible to share a sample where we can see all of this and help you with an expression which would work for you?

Re: Show intersection of row and column data

qliknexus_kgcru — Tue, 24 Jul 2018 13:08:58 GMT

For the FORECAST expression, i haven't quiet figured out on how i can show it. But i patterned it with the ACTUAL expression, the only difference would be the value for the DATE_ID.

it needs to correspond with the Transaction Month in the column. e.g. Transaction Month is OCT, DATE_ID is 20171001 for TM-0; Transaction Month is OCT, DATE_ID is 20170901 for TM-1; Transaction Month is NOV, DATE_ID is 20171001 for TM-1

The DATE_ID and Transaction month are a direct load. i'll make a sample data for this. Thanks Sunny!

Re: Show intersection of row and column data

qliknexus_kgcru — Tue, 24 Jul 2018 14:54:57 GMT

Hi Sunny,

I attached the sample data.

Re: Show intersection of row and column data

sunny_talwar — Tue, 24 Jul 2018 15:26:42 GMT

So, I only see Amount (sum(AMOUNT)) field which can be used a measure... Is this forecast or Actual? Where is the other one... Also, are we looking to solve forecast, actual or both? I

Re: Show intersection of row and column data

qliknexus_kgcru — Tue, 24 Jul 2018 15:47:15 GMT

Here is the sample app...

i only need the FORECAST also, i need to add a calculated quarter total per month

Re: Show intersection of row and column data

sunny_talwar — Tue, 24 Jul 2018 16:08:18 GMT

Is this what you imagined (without calculated quarter total per month part)

If it is... what do you mean when you say calculated quarter total per month....?

Re: Show intersection of row and column data

qliknexus_kgcru — Tue, 24 Jul 2018 16:48:54 GMT

For the quarter total, i need it to look like this.. without editing the data load.

Display Total partial sum per quarter and end of month

The FORECAST expression seems correct but the OCT value should be null since there is no 20181001 in the DATE_ID (FILTERS: FISCAL YEAR = 2018; FISC_MONTH = APR). Also, how can i apply the forecast when it is in N-1, N-2? thanks!

Re: Show intersection of row and column data

sunny_talwar — Tue, 24 Jul 2018 17:00:30 GMT

For Quarter total... add fiscal quarter as a dimension....

I am not sure why Oct should not be there? and what do we need for N-1, N-2?

Re: Show intersection of row and column data

qliknexus_kgcru — Wed, 25 Jul 2018 01:15:55 GMT

The reason why Oct 2018 should not have a value is that the range of DATE_ID is from 20171001 - 201800601. there is no date in the DATE_ID that will correspond to the Transaction date so the values of forecast and actual for July - Oct should be null. For the quarter total, is there a way where we can put the total without displaying the fisc_qtr_no dimension? kinda like the end result of this one below, except the formula would be e.g. Actual (OCT + NOV + DEC) / Forecast (OCT + NOV + DEC) = 3Q:

Display Total partial sum per quarter and end of month

For the N-1, N-2, the forecast value needs to correspond with the Transaction Date and (Date ID -1)

e.g. The Transaction Month is Nov, For N-0 it will correspond with the 20171101 Date_ID; but for N-1 under the same Transaction Month, it will take the value for 20171001 since DATE_ID -1.. same goes for the other months

for N-2, Transaction Month is Dec, For N-0 it will correspond with the 20171201 Date_ID; but for N-2 under the same Transaction Month, it will take the value for 20171001 since DATE_ID -2

stalwar1‌