topic Re: Comparing monthly data in QlikView

Comparing monthly data

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I am new to Qlikview and have a problem I'm struggling with.

I would like to compare data from the selected month with a previous month.

My data field has been set up as following: LOAD Date(Date, 'MM-YY') as DateAB,

What expression should I use in order to compare data from the selected month with the previous month?

Thanks in advance!

Re: Comparing monthly data

Anil_Babu_Samineni — Mon, 02 Jan 2017 12:02:09 GMT

May be this

Sum({<DateAB = {"$(='>=' & (Max(DateAB)-1) & '<=' & Max(DateAB))"}>} Sales)

By Month, Like below

Sum({<DateAB = {"$(='>=' & (Max(Month(DateAB))-1) & '<=' & Max(Month(DateAB)))"}>} Sales)

Re: Comparing monthly data

PrashantSangle — Mon, 02 Jan 2017 12:04:04 GMT

Hi,

for current Month

sum({<DateAB={"=$(=max(DateAB))"}>}sales)

for last mont

sum({<DateAB={"=$(=Date(Addmonths(max(DateAB)),-1),'MM-YY')"}>}sales)

Note : careful with date type.

Also serach on community for MTD,LYMTD to get better understing.

Regards,

Re: Comparing monthly data

Mon, 02 Jan 2017 12:20:19 GMT

Thank you for your swift response, it is much appreciated!

Your first formula is giving me the wrong information. If I select '11-16' (which is my highest included date) it gives me ALL the previous months combined. I would like to compare '11-16' with '10-16'. So if I select 11-16, it should give me the sum of the field, only for 10-16.

=Sum({<DateAB = {"$(='>=' & (Max(DateAB)-1) & '<=' & Max(DateAB))"}>} [>60 USD])

Second formula

Returns 0.00.

=Sum({<DateAB = {"$(='>=' & (Max(Month(DateAB))-1) & '<=' & Max(Month(DateAB)))"}>} [>60 USD])

It could be my fault that the formula's are not right, what is your input?

I will include all the information about my data field, it is highly possible that I did something wrong:

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';

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

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

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

LOAD Date(Date, 'MM-YY') as DateAB,

num#(Text(Date(Date, 'YYMM'))) as DateMMYY,

Re: Comparing monthly data

Mon, 02 Jan 2017 12:23:25 GMT

Thank you for your response!

your first formula indeed gives the value for the current month.

Thought the second formula returns 0.00 for me:

=sum({<DateAB={"=$(=Date(Addmonths(max(DateAB)),-1),'MM-YY')"}>}[>60 USD])

Thought this could be a mistake of me, I will include all the information about my data field, could you please tell me what I'm doing wrong?

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';

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

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

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

LOAD Date(Date, 'MM-YY') as DateAB,

num#(Text(Date(Date, 'YYMM'))) as DateMMYY,

Re: Comparing monthly data

Anil_Babu_Samineni — Tue, 03 Jan 2017 04:19:51 GMT

Then use this for Previous Month of Data after selection, What was the "[>60 USD]"

=Sum({$<DateAB = {"$(=Max(DateAB)-1)"}>} [>60 USD])

Re: Comparing monthly data

PrashantSangle — Tue, 03 Jan 2017 05:35:11 GMT

Hi,

I think I have misplaced closing bracket in set analysis.

update set analysis as below

for last month

sum({<DateAB={"=$(=Date(Addmonths(max(DateAB),-1),'MM-YY'))"}>}sales)

also check what was the output of below expression in text object.

=Date(Addmonths(max(DateAB),-1),'MM-YY')

Regards,

Re: Comparing monthly data

Tue, 03 Jan 2017 06:32:32 GMT

Thank you for your answer,

If I select '11-16', it gives me the result for 11-16 instead of the previous month (10-16).

The second one returns '10-11' for me if I select '11-16'.

It may also be important to note:

Every document I uploaded into Qv has a separate date.

Document 1: 9-16

Document 2: 10-16

Document 3: 11-16

And so on.

Also, the format that these dates have in excel: '31/07/16', being the last day of the month.

Regards,

Re: Comparing monthly data

Tue, 03 Jan 2017 06:33:51 GMT

Thank you for your answer,

The above expression returns a 0.00 for me, [>60 USD] is the field I want the sum for, it are just some dollar amounts.

It may also be important to note:

Every document I uploaded into Qv has a separate date.

Document 1: 9-16

Document 2: 10-16

Document 3: 11-16

And so on.

Also, the format that these dates have in excel: '31/07/16', being the last day of the month.

Regards,

Re: Comparing monthly data

MK9885 — Tue, 03 Jan 2017 14:08:00 GMT

Calculating Month-over-Month and Yoy Based on current selection

Edit the dimension and also check your Date format from the above link.

Re: Comparing monthly data

martynlloyd — Tue, 03 Jan 2017 16:14:34 GMT

Hi Vik,

Best practice is to create a month dimension in the load, and then to use a master calendar to flag which month is current and which is previous.

You could miss out the master calendar and start with

LOAD

Date(Date, 'MM-YY') as DateAB,

Month(Date(Date, 'MM-YY')) as MonthAB

...

If you are not comfortable with set analysis, then even simpler is:

LOAD

Date(Date, 'MM-YY') as DateAB,

IF(Month(Date(Date, 'MM-YY'))=Month(Today()),Amount,0) as CurrMonthAmount,

IF(Month(Date(Date, 'MM-YY'))=Month(Today())-1, Amount,0) as PrevMonthAmount,

...

Kind regards,

Marty.

Re: Comparing monthly data

Thu, 05 Jan 2017 07:37:14 GMT

Thank you for your answer Martyn,

I have processed your first option. What set analysis formula should I now use?

It may also be important to note:

Every document I uploaded into Qv has a separate date.

Document 1: 9-16

Document 2: 10-16

Document 3: 11-16

And so on.

Also, the format that these dates have in excel: '31/07/16', being the last day of the month.

Regards,

Re: Comparing monthly data

martynlloyd — Fri, 20 Jan 2017 15:24:50 GMT

Hi Vik Vik,

How are you getting on with this issue? I have been away for a few weeks, so sorry if you still waiting for a solution.

Kind regards

Marty.

Re: Comparing monthly data

Mon, 23 Jan 2017 07:54:01 GMT

Hello Martyn,

The matter isn't urgent for me. So I haven't found a correct answer yet.

There isn't really an update from my last answer to you.

So every feedback is still very welcome!

Thanks in advance,

Vik