Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhaveshp90
Creator III
Creator III

Calculate current month YTD minus previous month YTD

Hello,

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

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;


Capture.PNG


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


1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

30 Replies
sunny_talwar

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?

bhaveshp90
Creator III
Creator III
Author

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

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

sunny_talwar

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?

sunny_talwar

Check now....

bhaveshp90
Creator III
Creator III
Author

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

sunny_talwar

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

bhaveshp90
Creator III
Creator III
Author

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

1.PNG

But it is correct when selected individual project number;

Capture.PNG

sunny_talwar

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

bhaveshp90
Creator III
Creator III
Author

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.

1.PNG