Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pgrs4219
Contributor III
Contributor III

how to show latest 5 months of data dynamically?

Hi Community,

I have a requirement that i need to show the latest 5 months of data,here is the formula that am using that for that


= IF([Month End Date]>=$(#=Date(Max({1<Month_End_Date={"<=$(Date(vMonthEndSelected))"}>} Month_End_Date,5),'DD MMM YY'))

AND [Month End Date]<=Date(Date#('$(vMonthEndSelected)','DD MMM YY'))

,[Month End Date]

Here vMonthEndSelected=max(Month End Date)

But am unable to to show the latest 5 months of data dynamically.any one can help me where exactly the problem is please.

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date] = {"$(='>' & Date(AddMonths(Max([Month End Date]), -5), 'DD MMM YY) & '<=' & Date(Max([Month End Date]), 'DD MMM YY'))"}>} sales_amount)


Sum({$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date] = {"$(='>' & Date(AddMonths(Max([Month End Date]), -5), 'DD MMM YY) & '<=' & Date(Max([Month End Date]), 'DD MMM YY'))"}>} sales_amount)


Update: Dimension would just be a somple [Month End Date] field.

View solution in original post

12 Replies
cotiso_hanganu
Partner - Creator III
Partner - Creator III

I've notivce that (at least some times) Set Analysis on date records is tricky...

It seems to me that set analysis is not handling pretty well DUAL data sets.

Therefore, what I've found it's always working: create an additional column of data that is only the NUMERIC part (or define your own as a numerical one).

More specifically to your set of data, I would create a new column in the script, such as

num([Month End Date]) as [Month End Date Num]

and use the new column in the interface formulas instead of the old one.

Hope it helps

sunny_talwar

Are you using this as a calculated dimension?

prabiradhikary
Partner - Contributor III
Partner - Contributor III

Hi,

Set two Variable :

vMonthStartSelected = AddMonths(vMonthEndSelected, -5)

vMonthEndSelected = max(Month End Date)


use above variable in expression :

Sum({<DATE={'>=$(vMonthStartSelected )<=$(vMonthEndSelected )'}>} [Measure Name])

In dimension use Month.

pgrs4219
Contributor III
Contributor III
Author

yes sunny

sunny_talwar

You don't you use set analysis instead in your expression? I think set analysis tend to be more efficient in comparison to calculated dimensions. If you are able to provide me with your current expression, I might be able to give you something to try

pgrs4219
Contributor III
Contributor III
Author

here is the expressions am using

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z1"}>}sales_amount)

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z2"}>}sales_amount)

i want months in X-axis.

sunny_talwar

Try this:

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date] = {"$(='>' & Date(AddMonths(Max([Month End Date]), -5), 'DD MMM YY) & '<=' & Date(Max([Month End Date]), 'DD MMM YY'))"}>} sales_amount)


Sum({$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date] = {"$(='>' & Date(AddMonths(Max([Month End Date]), -5), 'DD MMM YY) & '<=' & Date(Max([Month End Date]), 'DD MMM YY'))"}>} sales_amount)


Update: Dimension would just be a somple [Month End Date] field.

sunny_talwar

Or try this as your calculated dimension:

=If([Month End Date] >= AddMonths(Max(TOTAL Month_End_Date), -5) and [Month End Date] <= Max(TOTAL Month_End_Date), [Month End Date])

Expressions:

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date]>}sales_amount)

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date]>}sales_amount)

pgrs4219
Contributor III
Contributor III
Author

thanks for your reply if i do this its not responding though i change the months.

=If([Month End Date] >= AddMonths(Max(TOTAL Month_End_Date), -5) and [Month End Date] <=Max(TOTAL Month_End_Date), [Month End Date])

Expressions:

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date]>}sales_amount)

Sum({$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date]>}sales_amount)