topic Re: Set Analysis for Current month Value minus Previous month Value in QlikView

Set Analysis for Current month Value minus Previous month Value

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

Hello,

I have the table as shown below;

I am trying to calculate the MTD Variance which is (Current month YTD) - (Previous month YTD) with the expression as;

Sum(YTD)

Sum({$< Snapshot_Date = {$(vPreviousMonth)}>}YTD)

where PreviousMonth is a variable defined as

Let vPreviousMonth = '=MonthEnd(AddMonths((Snapshot_Date),-1))';

The above expression returns null values, what wrong am I doing here? any help is appreciated

thanks

Re: Set Analysis for Current month Value minus Previous month Value

Anil_Babu_Samineni — Wed, 31 Oct 2018 04:50:38 GMT

Can you show the image with this variable vPreviousMonth in text box?

Re: Set Analysis for Current month Value minus Previous month Value

sunny_talwar — Wed, 31 Oct 2018 11:28:06 GMT

May be try this

LET vPreviousMonth = '=Date(Floor(MonthEnd(Snapshot_Date, -1)))';

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 12:40:13 GMT

This is how I defined the variable.

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 13:40:37 GMT

The values are still blank. I have used your variable and this expression;

Sum(YTD)

Sum({$< Snapshot_Date = {$(vPreviousMonth)}>}YTD)

Re: Set Analysis for Current month Value minus Previous month Value

sunny_talwar — Wed, 31 Oct 2018 13:46:24 GMT

Try this

Sum(YTD) - Sum({$< Snapshot_Date = {"$(vPreviousMonth)"}>}YTD)

Sum(YTD) - Sum({$< Snapshot_Date = {"$(=vPreviousMonth)"}>}YTD)

Sum(YTD) - Sum({$< Snapshot_Date = {"$(=$(vPreviousMonth))"}>}YTD)

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 14:03:05 GMT

I've tried the expressions which are showing as null values. Attached the file for your reference.

Re: Set Analysis for Current month Value minus Previous month Value

sunny_talwar — Wed, 31 Oct 2018 14:07:44 GMT

There is something wrong about your expression.... you have Sum( over Sum( without Aggr()

Sum({$< Snapshot_Date = {"$(vPreviousMonth)"}>}

(If(Num_Month = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0,

((If(Num_Month = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum({<Cost_Area = {'Mechanical Engineering (labor + travel)'}>}Updated_OEC_Budget)))

(If(Num_Month = '$(vPreYearLastMonth)' and Year = '$(vPreYear)', 0, Sum({<Cost_Area = {'Mechanical Engineering (labor + travel)'}>}Forecast_Cost_to_Complete))))

((Sum(TOTAL <Cost_Area> {<Year = {'$(vPreYear)'} , Num_Month = {'$(vPreYearLastMonth)'}, Cost_Code = {'3'}, Snapshot_Date = >}Updated_OEC_Budget))

(Sum(TOTAL <Cost_Area> {<Year = {'$(vPreYear)'} , Num_Month = {'$(vPreYearLastMonth)'}, Cost_Code = {'3'}, Snapshot_Date = >}Forecast_Cost_to_Complete)))))

What exactly are you trying to do here?

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 14:12:50 GMT

I have that expression to calculate my YTD.

The MTD should be Current month YTD - Previous month YTD as shown below;

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 14:15:53 GMT

I am testing the MTD to get the previous month YTD, so that I can subtract that with the current month YTD.

Re: Set Analysis for Current month Value minus Previous month Value

sunny_talwar — Wed, 31 Oct 2018 14:23:13 GMT

Check attached.... you don't need set analysis here... but Inter-Record Functions

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 14:38:49 GMT

Ah, thank you stalwar1‌ for the information. This is what I am looking for

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Wed, 31 Oct 2018 17:51:03 GMT

stalwar1‌ when we use Above() here it is calculating per row basis (current row minus previous row) not considering the project numbers and country selected. Any idea on how to avoid this?

Re: Set Analysis for Current month Value minus Previous month Value

sunny_talwar — Thu, 01 Nov 2018 10:55:24 GMT

Can you share this in a qvw file and also make sure to provide the expected output

Re: Set Analysis for Current month Value minus Previous month Value

bhaveshp90 — Thu, 01 Nov 2018 14:43:31 GMT

stalwar1‌ Since this thread has been closed I have created another question. Please find the link here Calculate current month YTD minus previous month YTD

thank you

Re: Set Analysis for Current month Value minus Previous month Value

sunny_talwar — Thu, 01 Nov 2018 14:51:32 GMT

Okay, looking at that now