topic Re: Current Month V Previous Month in QlikView

Current Month V Previous Month

mohiahmed — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I need some help with a query and any help would be greatly appreciated. Trying to show current month v previous month as calculations and finding it difficult. Table 1 I have main numbers and table 2 I am trying to show the difference and don't know where I am going wrong.

Table 1

Table 2

Query in expression is sum({<[Reporting Month]={">=$(=monthstart(today()))"} >} ['MI Requirement Volumes'])-sum({<[Reporting Month]={">=$(=monthstart(today(),-12))"} >} ['MI Requirement Volumes'])

Regards

Mohi

Re: Current Month V Previous Month

sunny_talwar — Wed, 14 Dec 2016 15:25:19 GMT

You will either have to use Missing Manual - Before() and After() or use The As-Of Table

Re: Current Month V Previous Month

vinieme12 — Wed, 14 Dec 2016 15:52:28 GMT

TRY

sum({<[Reporting Month]={">=$(=monthstart(today()))"} >} ['MI Requirement Volumes'])-sum({<[Reporting Month]={">=$(=monthstart(addmonths(today(),-1)))"} >} ['MI Requirement Volumes'])

Re: Current Month V Previous Month

mohiahmed — Wed, 14 Dec 2016 15:56:08 GMT

Thanks for the Vineeth, however I only get attached result now.

Re: Current Month V Previous Month

sunny_talwar — Wed, 14 Dec 2016 15:56:35 GMT

I have feeling it is not going to work vinieme12‌ because Date is one of the dimension. So this would show two columns, 1) for 01/01/2016 and another one for 01/01/2015 and will never put them in one column unless we use Before.

Re: Current Month V Previous Month

mohiahmed — Wed, 14 Dec 2016 15:58:06 GMT

Thanks for that Sunny.Tying to get my head round this Sunny bit finding it difficult. Need to do more reading around it.

Re: Current Month V Previous Month

sunny_talwar — Wed, 14 Dec 2016 16:03:53 GMT

If you are able to share a sample, we might be able to help you better here

Re: Current Month V Previous Month

mohiahmed — Thu, 15 Dec 2016 08:15:48 GMT

Hi Sunny, this is my load script

SQL

SELECT `Calculation Type`,
Customer,
`Customer System ID`,
`First Period End Date`,
`FM Description`,
`FM ID`,
`FM Type`,
Frequency,
`GAG Indicator`,
`Group ID`,
Model,
`Number of Text Components`,
`Number of Tick Components`,
`Number of Work Items per Annum`,
`Post GAG TtC DG`,
Region,
`Relationship Owner`,
`Reporting Month`,
SBU,
Team,
Type
FROM `353_Stock`
WHERE ((([353_Stock].SBU) Not In ('BA&SF')));

Re: Current Month V Previous Month

sunny_talwar — Thu, 15 Dec 2016 10:02:58 GMT

When I said sample, I meant your qvw file and not your script my friend. This script is not very helpful for me as I cannot run this and create the same visual like yours

Re: Current Month V Previous Month

mohiahmed — Thu, 15 Dec 2016 10:10:54 GMT

Aha, ok sorry matey; Let me send it over.

Re: Current Month V Previous Month

sunny_talwar — Thu, 15 Dec 2016 10:12:19 GMT

Re: Current Month V Previous Month

mohiahmed — Thu, 15 Dec 2016 10:31:23 GMT

Hi Sunny, I have attached the qvw file. It has dummy data, your help is greatly appreacitaed

Re: Current Month V Previous Month

sunny_talwar — Thu, 15 Dec 2016 10:36:43 GMT

Dummy data is good, but this only has data for 2016? You want to see 2016-2015 right? How will I implement to show the change when you don't even have any data from 2015 .

Re: Current Month V Previous Month

mohiahmed — Thu, 15 Dec 2016 10:40:25 GMT

Hi Sunny, I don;t have any data for 2015. There will be ongoing from 2016 onwards. need to be able to show Current month v previous month for now. Also, will have filter for year for next year once I start getting the data

Re: Current Month V Previous Month

sunny_talwar — Thu, 15 Dec 2016 10:43:06 GMT

Got it

Re: Current Month V Previous Month

sunny_talwar — Thu, 15 Dec 2016 10:44:52 GMT

Here is one option

Sum(['MI Requirement Volumes'])-Before(Sum(['MI Requirement Volumes']))

Will work on the AsOfTable Method at a later time and will respond back

Re: Current Month V Previous Month

mohiahmed — Thu, 15 Dec 2016 10:47:58 GMT

Thanks Sunny, so thats the Before Sum you mentioned. I just didn't see that at all. Many Many thanks for that and your help has been deeply appreciated.