Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
NewQlikUser
Contributor II
Contributor II

Display last 6 months in a bar char based on current selection

I am trying to create a very dynamic dashboard where users can select any month and a financial year and get sales results by month and products.

Currently it is static and there is no selection involve. I am using the following formula to show the last 6 months in a bar chart :

=sum({<Date={'>=$(=Date(MonthStart(AddMonths(vToday, -6))))<=$(=Date(MonthEnd(AddMonths(vToday, -1))))'},
S_PROD_GRP={'Saver', 'Insurance'}>} Sales) /1000

If user select month and year then how I should do expression script to get the following results (attached pictures)

this is current month expression on a text object that works really well:

=sum({1<FinYear={'$(=GetFieldSelections([FinYear]))'}, CalendarMonth={'$(=GetFieldSelections([CalMonth]))'}, S_PROD_GRP={'Saver','Insurance'}>} Sales)

1 Solution

Accepted Solutions
Vegar
MVP
MVP

OK.
You make period selections only in month and fiscal year? Do you have a field containing date information (Called Datefield in my example below)?
Are all your fields connected into your data model?

If yes then this should work:

=sum({<FiscalYear, Month, Datefield={'>=$(=Date(MonthStart(AddMonths(MAX(Datefield), -5))))<=$(=Date(MonthEnd(AddMonths(MAX(Datefield), 0))))'},
S_PROD_GRP={'Saver', 'Insurance'}>} Sales) /1000

View solution in original post

4 Replies
Vegar
MVP
MVP

Try this
=sum({<Date={'>=$(=Date(MonthStart(AddMonths(MAX(Datefield), -6))))<=$(=Date(MonthEnd(AddMonths(MAX(Datefield), -1))))'},
S_PROD_GRP={'Saver', 'Insurance'}>} Sales) /1000
NewQlikUser
Contributor II
Contributor II
Author

Thank for your answer but I need to use getfieldselections function to get it work as users will be selection any month or fiscal year and the chart should be changing according to the selection.

The answer you provide doesn't really work. Sorry if I wasnt clear enough on what i am trying to get. Basically o want to be able to display the last six months of data based on the month and fiscal year selection.

Vegar
MVP
MVP

OK.
You make period selections only in month and fiscal year? Do you have a field containing date information (Called Datefield in my example below)?
Are all your fields connected into your data model?

If yes then this should work:

=sum({<FiscalYear, Month, Datefield={'>=$(=Date(MonthStart(AddMonths(MAX(Datefield), -5))))<=$(=Date(MonthEnd(AddMonths(MAX(Datefield), 0))))'},
S_PROD_GRP={'Saver', 'Insurance'}>} Sales) /1000
NewQlikUser
Contributor II
Contributor II
Author

That is perfect. Thank you so much for your help.