Show intersection of row and column data

[qliknexus_kgcru]

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‌

[sunny_talwar]

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?

[qliknexus_kgcru]

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!

[qliknexus_kgcru]

Hi Sunny,

I attached the sample data.

[sunny_talwar]

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

[qliknexus_kgcru]

Here is the sample app...

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

[sunny_talwar]

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....?

[qliknexus_kgcru]

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!

[sunny_talwar]

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?

[qliknexus_kgcru]

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‌