Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

pgrs4219
New Contributor II

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

12 Replies
cotiso_hanganu
Contributor

Re: how to show latest 5 months of data dynamically?

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

MVP
MVP

Re: how to show latest 5 months of data dynamically?

Are you using this as a calculated dimension?

prabiradhikary
New Contributor III

Re: how to show latest 5 months of data dynamically?

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
New Contributor II

Re: how to show latest 5 months of data dynamically?

yes sunny

MVP
MVP

Re: how to show latest 5 months of data dynamically?

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
New Contributor II

Re: how to show latest 5 months of data dynamically?

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.

MVP
MVP

Re: how to show latest 5 months of data dynamically?

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.

MVP
MVP

Re: how to show latest 5 months of data dynamically?

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
New Contributor II

Re: how to show latest 5 months of data dynamically?

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)