Trying to show Daily and Monthy Comparison between Current Year and Previous Year in a Pivot table in QlikSense

[#1]

Hi All,

I am trying to show Daily and Monthy Comparison between Current Year and Previous Year in a Pivot table in QlikSense.

We have all the Calendar fields (Date, Month, Year, Month-Year etc) in a dimension called DimDate. We have a Fact table storing the transactions. Date is common between DimDate and Fact.

I tried to show the following in Pivot table and did not succeed.

Month-Year

Date

Turnover

Turnover Last Year

I tried using set analysis, but did not succeed as the date related tables are present in the chart.

Is there a way do it?

Below is an example of How it should look like.

Month	Date	Machine Turnover	Machine Turnover Last year	Variance
Jan-2017		$10,754,608	$10,753,452	$1,156
Feb-2017		$9,331,047	$9,522,667	-$191,620
Mar-2017		$11,239,822	$10,111,051	$1,128,771
Apr-2017		$10,341,098	$10,613,375	-$272,276
May-2017		$11,166,579	$9,722,408	$1,444,171
Jun-2017		$7,142,170	$10,197,236	-$3,055,067
(when expanded to date)	01/06/2017	$370,590	$276,170	$94,420
	02/06/2017	$829,450	$601,126	$228,324
	03/06/2017	$1,250,095	$1,048,779	$201,316

[plexpro52]

Hello Pradosh,

Attached is a trimmed-down app with an example of what I am experiencing.

- The first measure is the sum for the current year.

- The second measure is one of my attempts to get the sum for the year prior to the current column value.

- The third measure is Mahi's suggestion to get the sum for the year prior to the current column value.

- The second measure is your suggestion to get the sum for the year prior to the current column value.

Feel free to play with it, and please let me know if you make progress.  I will also post back if I find a solution.

René

[plexpro52]

Hello Mahi,

Please see the app that I uploaded at Pradosh's request, in case it interest you.

Thanks!

René

[pradosh_thakur]

Hi

I tried your sample.If you use Year as column it will always so 0 for previous quarter.

1:so you can do this create a data island for invoice year as invoice_1 and use it as column

something like

select distinct invoice_year_field from table name;

or you can create an inline table and use it in column. if you use same year field in row it will show zero value as it will only correspond to that value and that value only

2:in expression put as

Sum({$<Invoice_Year={  $(=max(Invoice_Year)-1) }>}ItemSalesAmt)

and

Sum({$<Invoice_Year={  $(=max(Invoice_Year)) }>}ItemSalesAmt)

let me know if you need any more help. I could not reload the app so i didn't do else would have updated the app and send it to you.

[NZFei]

See attached.

I normally do this in the script (the part I have commented out at the beginning of the script) if the sales dataset is not supper big.

Actually typically we report the previous sales by month so we can pre-aggregate the sales by month and then concatenate it to the main fact table.

Hope it can help.

Fei

[plexpro52]

Hello Fei,

Thanks for your assistance.  However, your example shows straight tables, and I have had no problem showing the comparison of current to prior year summaries with any visualization type but pivot tables.

Pivot tables, at least when defining columnar dimensions, appear to work differently.  Your solution does appear to work differently than what I have done with straight tables, and it does produce non-zero numbers, but not exactly what I expected; I'm in the process of verifying it now.

Thanks; I'll post back when I have something definitive.

René

[plexpro52]

Hello Fei,

Your solution works for all but the current year.  It sums up only through the current date, so it gives the equivalent of Last-Year-YTD, instead of last year's full amount.

I haven't yet figured out why.

René

[plexpro52]

By changing the definition of the pivot table's Column field from using Invoice Date to using the Master Calendar date, i.e., from

=if(Invoice_Year>$(vCurrentYear)-5,Invoice_Year,null())

to

=if(MC_Year>$(vCurrentYear)-5 and MC_Year<=$(vCurrentYear),MC_Year,null())

where MC_Year is an attribute of the Master Calendar, the key of which (MC_Date) is a foreign key on the DateBridge table, it now includes all of last year's invoices in calculating the sum for comparison to this year.  This begs the question, is that what I want?  Perhaps last year's year-to-date in comparison with this year be better.  But at least I know how to get it now.

Thanks for your assistance.

That all being said, I would prefer not to have to pre-aggregate this in the load script; if anyone have any further ideas about how to do it in the chart, I am very interested in hearing from you.

Thanks!

René

P.S.  By the way, I am attaching the version of the app that implements the solution.  The changes that I made are in the last two tabs, and are indicated by /* Additional code added pursuant Fei Xu's recommendations */

[plexpro52]

Trying these various means of aggregating ItemSalesAmt for the year previous to the Invoice Year in focus (the column in the pivot table):

A. Sum({$<DateType={'LY-Invoice'}>}ItemSalesAmt)

B. Sum({$<DateType={'LY-Invoice'},Invoice_Year={"=$(=max(Invoice_Year)-1)"}>}ItemSalesAmt)

C. Sum({$<DateType={'LY-Invoice'},Invoice_Year={  $(=max(Invoice_Year)-1) }>}ItemSalesAmt)

D. Sum({$<DateType={'LY-Invoice'},Invoice_Year={" $(=max(Invoice_Year)-1)"}>}ItemSalesAmt)

Where Invoice_Year

• = 2017:  A, B, & C work.
• < 2017:  A & B work.

So, the bottom line, I added to the load script to set some of this up:

Concatenate(DateBridge)

LOAD GlobalTransKey

          ,'LY-' & TranType as DateType

          ,AddYears(InvoiceDate,1) as MC_Date

Resident TxnHeader

WHERE TranType='Invoice';

So essentially what I did was add a different date to the invoice transaction.  Then in the visualizations, I just used option A (or a variant thereof, for different measures) to sum up the sales.

Use of the Master Calendar and a Date Bridge table linking dates of various types between the M.C. and the transaction, ends up being a fairly simple tweak to get what I need in a pivot table.

However, I still think that there must be some way to make this work in a pivot table without introducing a new date type...  If anyone discover or already know a technique to accomplish this, I would love to hear about it!

Thanks,

René