topic Re: Trying to show Daily and Monthy Comparison between Current Year and Previous Year in a Pivot table in QlikSense in App Development

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

Tue, 01 Aug 2017 07:19:41 GMT

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

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

plexpro52 — Thu, 12 Oct 2017 16:01:27 GMT

Hello Mahidhar,

Did you ever solve your problem?

I am attempting something similar, with

SKU as a row.
Invoice_Year as a column, which groups the measures for each year.
Total_invoiced_this_year as measure.
Total_invoiced_last_year as measure.

The expressions for current year and last year both produce the same value, and it is the correct value for the current year:

Sum(ItemSalesAmt)

Sum({$<Invoice_Year={"=only(Invoice_Year)"}>}ItemSalesAmt)

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

All three produce the same result.

René

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

marcoyukon — Thu, 12 Oct 2017 20:16:32 GMT

Qlik introduced Calendar measures into the tool itself and I found it easier to use than using a Date Dim, since a lot of the time intelligence calculations are already baked into the tool. Use the tool to create the calendar measures that you want. You can preview the set expression generated to see what it is doing under the hood. The only downside a see is that the aggregations are calculated on the fly, rather than pre-loaded when you use your own date dim. But if your fact dataset is not too big, it may be a good option. http://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Measures/create-calendar-measure.htm

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

plexpro52 — Fri, 13 Oct 2017 14:05:26 GMT

Hello Marco,

Thanks for your reply (although I'm not sure whether it be to me or to Mahidhar).

I am and have been using my own date calendar successfully for Invoice Date. It seems to be only with pivot tables that I run into complications...

However, I see that Calendar measures are a new thing, I will see whether I can find anything useful to apply to pivot tables.

René

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

Sat, 14 Oct 2017 03:07:48 GMT

Hi Rene,

If you don't use Year in the Dimensions, it works. Below is the screenshot.

The moment, you introduce "Year" Dimension, it will restrict the calculation of the measure value based the dimension and you cannot have the current year and last year measures next to each other. That is the reason, I haven't used the Year Dimension. By name of the Measure, Current year and Last year Measures are identified.

Assuming that Year is a number like 2017 in your case as well, below should work for you:

For Current Year: Sum({$<Invoice_Year={$(=max(Invoice_Year))}>}ItemSalesAmt)

For Previous Year: Sum({$<Invoice_Year={$(=max(Invoice_Year)-1)}>}ItemSalesAmt)

Regards,

Mahi

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

pradosh_thakur — Sat, 14 Oct 2017 05:10:46 GMT

try this

For Current Year: Sum({$<Invoice_Year={"$(=max(Invoice_Year))"}>}ItemSalesAmt)

For Previous Year: Sum({$<Invoice_Year={"$(=max(Invoice_Year)-1)"}>}ItemSalesAmt)

regards

Pradosh

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

plexpro52 — Mon, 16 Oct 2017 15:49:36 GMT

Hello Mahidhar,

Thank you for your response. I had also tried your suggestion, but I tried again, with the same result:

What I tried:

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

What you suggested:

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

What Pradosh suggested:

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

With my formula, the result for last year was identical to the result for this year, is if it were misidentifying the current year's data as for the prior year.

[Edited]

With your formula, as well as Pradosh's formula, the result was zero for 2017, as if for each column the data set in view were only for 2016 (because the formula always resolves the year to 2016, as can be seen from the heading), and so nothing from 2016 were found. But for the 2016 column, since that data is available, it was summed, showing that the formula is not adjusting to the context of the current value of the column but continues to calculate a result for 2016, which is the year in focus.

I am mystified.

René

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

plexpro52 — Mon, 16 Oct 2017 15:51:27 GMT

Hello Pradosh,

Thank you for your response. I had also tried your suggestion, but I tried again, with the same result:

What I tried:

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

What Mahidhar suggested:

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

What you suggested:

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

With my formula, the result for last year was identical to the result for this year, is if it were misidentifying the current year's data as for the prior year.

[Edited]

For the 2016 column, in contrast to Mahi's formula, it remained zero, the reason for which I don't know.

I am mystified.

René

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

pradosh_thakur — Mon, 16 Oct 2017 16:02:36 GMT

can you post a sample for use to try?

regards

Pradosh

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

plexpro52 — Mon, 16 Oct 2017 16:24:25 GMT

Hello Pradosh,

>> can you post a sample for use to try?

I will see. The data is sensitive, and I would need to restrict the load, but it may be possible.

In the meantime, please note the correction in my prior response to you.

Thanks!

René

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

plexpro52 — Mon, 16 Oct 2017 20:50:58 GMT

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é

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

plexpro52 — Mon, 16 Oct 2017 20:52:00 GMT

Hello Mahi,

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

Thanks!

René

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

pradosh_thakur — Mon, 16 Oct 2017 21:51:28 GMT

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.

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

NZFei — Mon, 16 Oct 2017 22:59:59 GMT

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

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

plexpro52 — Tue, 17 Oct 2017 15:59:23 GMT

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é

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

plexpro52 — Tue, 17 Oct 2017 17:35:36 GMT

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é

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

plexpro52 — Tue, 17 Oct 2017 18:33:46 GMT

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())

=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 */

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

plexpro52 — Thu, 19 Oct 2017 19:11:55 GMT

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é