Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
jacobluft
New Contributor III

Differences based on Date and Sales

Hey guys, I'm trying to create visuals that show differences from a month to month in a single metric. Similar to what is happening in this sample table I made in excel.

Capture.PNG

My script currently looks like this.


SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='M/DD/YY';

SET TimestampFormat='M/DD/YY hh:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-CA';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Report:

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is US);

concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is EMEA);

concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is CANADA);

concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is Canada);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is EMEA);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is USA);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is CanadaApril);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is EMEAApril);

Concatenate

LOAD *,Date(MonthStart(Date),'MMM-YY') as Month FROM [FILE]

(ooxml, embedded labels, table is USA);

Set vLastMonth="=Date(Max(Month),'MMM-YY')";

Set v2ndLastMonth="=Date(addMonth(Max(Month),-1),'MMM-YY')";

And my expression in my table is

Sum({$<Month={"$(vLastMonth)"}>}[Seat Count]) - Sum({$<Month={"$(v2ndlastMonth"}>}[Seat Count])

How would I go about doing what I need? Should I be using a master calendar and, if so, how do I format my expression using a Calendar to give me the results I need?

Thanks!

1 Solution

Accepted Solutions
MVP
MVP

Re: Differences based on Date and Sales

SUM(Sales) - Above(SUM(Sales))

9 Replies
MVP
MVP

Re: Differences based on Date and Sales

SUM(Sales) - Above(SUM(Sales))

jacobluft
New Contributor III

Re: Differences based on Date and Sales

Hey,

This works for just dates but what about if I wanted to expand it into multiple dimensions, such as below?

Capture.PNG

MVP
MVP

Re: Differences based on Date and Sales

Provide some more info

What is dimensions

What is expression here

jacobluft
New Contributor III

Re: Differences based on Date and Sales

The dimension in this case is Countries and Dates.

The expression is the difference in [Seat Count] from one date to the next for each country.

The [Expired] expression in the above screenshot is irrelevant to the Difference calculation as it should only be pertaining to [Seat Count].

Highlighted
jacobluft
New Contributor III

Re: Differences based on Date and Sales

The Object to the left is the table I am trying to create. The object to the right is the test object I made using you initial response.

MVP
MVP

Re: Differences based on Date and Sales

Can you provide few line of sample data to work?

jacobluft
New Contributor III

Re: Differences based on Date and Sales

Here's some sample data!

jacobluft
New Contributor III

Re: Differences based on Date and Sales

Would it be smarter to just create a new Excel column using DAX arithmetic to do this? Seems like QlikView doesn't support this type of expression in a way that makes it worth doing in their program instead of in the file that's being imported.

jacobluft
New Contributor III

Re: Differences based on Date and Sales

This worked after I rearranged my dimensions, thanks!