Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. 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))

View solution in original post

9 Replies
MVP
MVP

Re: Differences based on Date and Sales

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

View solution in original post

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

Highlighted
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].

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!