topic Re: Calculate current month YTD minus previous month YTD in QlikView

Calculate current month YTD minus previous month YTD

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

Hello,

I have the table as shown below with YTD and MTD as expressions.

MTD = (Current month YTD) - (Previous month YTD).

My expression for MTD = YTD - Alt(Above(Total(YTD)), 0).

This expression is calculating correctly when selecting individual project number, but for all the project numbers selected It is calculating on per row basis which is completely wrong as shown in the below image;

Any idea on how to avoid it? (I want to calculate MTD as current month YTD - previous month YTD per Project_Number)

attached are files for your reference;

thanks

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 14:54:58 GMT

In your previous post I created a MonthYear field in the script, is there a reason you don't want to create MonthYear in the script and create it directly in the chart?

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Thu, 01 Nov 2018 15:02:36 GMT

I have removed that tab which has the script. You can still use if needed;

Date(MonthStart(Snapshot_Date), 'MMMM-YYYY') as MonthYear,

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 15:03:58 GMT

I can add this, but the point is are you going to add this to your app? I mean is there a reason you don't want to have this in your app?

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 15:12:51 GMT

Check now....

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Thu, 01 Nov 2018 15:14:48 GMT

It is not included in this test file. I will include this in my prod application, there is no reason not to include it.

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 15:16:30 GMT

Okay sounds good, I just wanted to know if there are concerns. But good to know that there aren't any.

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Thu, 01 Nov 2018 15:56:14 GMT

stalwar1‌ the YTD and MTD are same when filtered by Date, look at this below;

But it is correct when selected individual project number;

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 16:02:18 GMT

Yes sir, because the project's are not repeating when you select a date

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Thu, 01 Nov 2018 16:08:43 GMT

Okay, But the values are not correct when filtered by Date. Any idea on how to avoid it?

This is how the output should be when selected September month.

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 16:11:06 GMT

I guess I get your point. You will need to ignore selection in Snapshot_Date, MonthYear or any other date and time field where you are making selection and then multiply your whole expression with Avg(1).

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Thu, 01 Nov 2018 16:16:05 GMT

Here is your example.... didn't need to multiply by Avg(1) because we were using Aggr(). But other than that you have unnecessarily made your expression super complicated... why do you need Num() function 20 times (exaggerated, but you get the point). Try the remove unnecessary functions to make your expression easier to understand

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Fri, 02 Nov 2018 13:57:39 GMT

stalwar1‌ thank you So much for it. I tried to put in my application but something is failing out. Can you tell me where I am doing wrong? Attached is for your reference. thank you

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Fri, 02 Nov 2018 14:02:16 GMT

I tried to put in my application but something is failing out. Can you tell me where I am doing wrong?

Hahahaha do you want to may be tell me what is failing out? I am not 'Antaryami' brother

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Fri, 02 Nov 2018 14:18:42 GMT

Sorry if you felt wrong for my saying. I am trying and couldn't able to find out what's is wrong. But your solution is exactly what I want. thank you again.

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Fri, 02 Nov 2018 14:20:25 GMT

Hey what happened bhaveshp90‌? I was just asking about that is not right in the new attachment brother? You didn't like my comment? I was just trying to add humor.

Anyways I am sorry if you feel offended.

Best,

Sunny

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Fri, 02 Nov 2018 14:25:32 GMT

Not like that brother. I am thinking you might feel bad for what I have asked you. I didn't feel offended. I agree that it is not correct to ask like that.

You need not be sorry stalwar1‌. I thank you for your solution and quick response every time.

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Fri, 02 Nov 2018 14:25:55 GMT

See, I like your comment too

Re: Calculate current month YTD minus previous month YTD

sunny_talwar — Fri, 02 Nov 2018 14:28:11 GMT

There is no reason for me to feel bad about anything you ask. Once again, my question to you was -> What makes you think that the file you attached is not working? Can you give me an example where you see issues? All I was trying to say was that without you giving me this information, it is hard (time consuming and hence less efficient) for me to go in and try to figure out what isn't working the way it should. But if you can tell me, I can quickly go in and check to see what the issue is and how we can resolve it.

Does that make sense?

no hard feelings at all

Re: Calculate current month YTD minus previous month YTD

bhaveshp90 — Fri, 02 Nov 2018 14:36:25 GMT

Yes, I understand. When I use the same expression in my application the for MTD it is not working. Look at how the values are; Can you look into this expression for MTD?