Chart to compare Month selected to previous month

[ivandrago]

Hi,

I have the attached document which shows a bar chart for This Month= '01-NOV-2015' and Last Month= '01-OCT-2015', I want the current month to be based on the DateMonth that is selected by the user and then the other measurement (Last Month) to be the month before this.

Any ideas?

Thanks

[sunny_talwar]

A small change in the script so that your dateMonth field can be read as a date:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MMM-YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

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

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

And changes to the expression, once the above change is refreshed:

Expression for This Month: =Sum({<DateMonth = {"$(=Date(Max(DateMonth)))"}>}Amount)

Expression for Last Month: =Sum({<DateMonth = {"$(=Date(AddMonths(Max(DateMonth), -1)))"}>}Amount)

[PradeepReddy]

see the attachment...

[ivandrago]

Any reason why the chart on the attached document is not working?

[sunny_talwar]

Things that are needed to changed:

• You need SET DateFormat='DD-MMM-YYYY'; so that your date format can be understood by QlikView
• You don't have a field called Payroll Month in the application you shared

See if the attached helps.

Best,

Sunny

[PradeepReddy]

there is no field 'Payroll Month' in your data model, but you are using it in expression

[ivandrago]

Is

SET DateFormat='DD-MMM-YYYY'

The only way to get round this? Is there another way?

[sunny_talwar · Accepted solution]

Other alternative is to do something like this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

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

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

Fact:

LOAD ID,

Date#(DateMonth, 'DD-MMM-YYYY') as DateMonth,

  Amount,

  Manufacturer,

  Type,

  Ver,

  Pick

INLINE [

    ID, DateMonth, Amount, Manufacturer, Type, Ver, Pick

    1, 01-JUN-2015, 500, B, OFF, LIVE, JS,

    2, 01-JUN-2015, 600, A, ON, LIVE, JS,

    3, 01-JUN-2015, 400, C, OFF, LIVE, JS,

    4, 01-JUL-2015, 100, C, ON, LIVE, JT,

    5, 01-JUL-2015, 150, F, OFF, LIVE, JT,

    6, 01-JUL-2015, 250, D, ON, LIVE, JT,

    7, 01-AUG-2015, 400, C, OFF, LIVE, JT,

    8, 01-AUG-2015, 700, F, ON, LIVE, JT,

    9, 01-AUG-2015, 600, C, OFF, LIVE, JT,

    10, 01-SEP-2015, 900, D, ON, LIVE, JT,

    11, 01-SEP-2015, 250, A, OFF, LIVE, JS,

    12, 01-SEP-2015, 300, C, ON, LIVE, JS,

    13, 01-OCT-2015, 100, B, OFF, LIVE, JS,

    14, 01-OCT-2015, 600, C, ON, LIVE, JT,

    15, 01-OCT-2015, 700, B, OFF, LIVE, JT,

    16, 01-NOV-2015, 100, B, ON, LIVE, JT,

    17, 01-NOV-2015, 200, C, OFF, LIVE, JT,

    18, 01-NOV-2015, 300, B, ON, LIVE, JT

];

Concatenate

LOAD ID,

  Date#(DateMonth, 'DD-MMM-YYYY') as DateMonth,

  Amount,

  Manufacturer,

  Type,

  Ver,

  Pick

INLINE [

  ID, DateMonth, Amount, Manufacturer, Type, Ver, Pick

    1, 01-JUN-2015, 500, B, OFF, ARCHIVE, JS,

    2, 01-JUN-2015, 200, A, OFF, ARCHIVE, JS,

    3, 01-JUN-2015, 400, C, OFF, ARCHIVE, JS,

    4, 01-JUL-2015, 100, C, ON, ARCHIVE, JT,

    5, 01-JUL-2015, 150, F, OFF, ARCHIVE, JT,

    6, 01-JUL-2015, 250, D, ON, ARCHIVE, JT,

    7, 01-AUG-2015, 400, C, OFF, ARCHIVE, JT,

    8, 01-AUG-2015, 700, F, ON, ARCHIVE, JT,

    9, 01-AUG-2015, 600, C, OFF, ARCHIVE, JT,

    10, 01-SEP-2015, 900, D, ON, ARCHIVE, JT,

    11, 01-SEP-2015, 250, A, OFF, ARCHIVE, JS,

    12, 01-SEP-2015, 300, C, ON, ARCHIVE, JS,

    13, 01-OCT-2015, 100, B, OFF, ARCHIVE, JS,

    14, 01-OCT-2015, 600, C, ON, ARCHIVE, JT,

    15, 01-OCT-2015, 700, B, OFF, ARCHIVE, JT,

    16, 01-NOV-2015, 100, B, ON, ARCHIVE, JT,

    17, 01-NOV-2015, 200, C, OFF, ARCHIVE, JT,

    18, 01-NOV-2015, 300, B, ON, ARCHIVE, JT

];

SortMonth:

LOAD Date#(DateMonth, 'DD-MMM-YYYY') as DateMonth,

  PeriodKey

INLINE [

    DateMonth, PeriodKey

    01-JUN-2015, 1

    01-JUL-2015, 2

    01-AUG-2015, 3

    01-SEP-2015, 4

    01-OCT-2015, 5

    01-NOV-2015, 6

    ];

and then use this expression for current month:

=If(Pick='JS', Sum({<Type={'OFF'}, DateMonth = {"$(=Date(Max(DateMonth), 'DD-MMM-YYYY'))"} >} Amount),

If(Pick='JT', Sum({<Type={'ON'}, DateMonth = {"$(=Date(Max(DateMonth), 'DD-MMM-YYYY'))"}>} Amount)))

[ivandrago]

Hi Sunny T,

Thanks for your help so far, how do I add another expression for the Prev Month?

Thanks

[sunny_talwar]

May be this:

=If(Pick='JS', Sum({<Type={'OFF'}, DateMonth = {"$(=Date(AddMonths(Max(DateMonth), -1), 'DD-MMM-YYYY'))"} >} Amount),

If(Pick='JT', Sum({<Type={'ON'}, DateMonth = {"$(=Date(AddMonths(Max(DateMonth), -1), 'DD-MMM-YYYY'))"}>} Amount)))