Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

businessintelli
New Contributor III

Current Month , Current and previous years

Hello,

My chart look like this Capture.PNG

Measures that I use :

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),0)) <=$(=AddYears(Date(Today()),0))"}>}FactSoldAmount)  --- for 2018

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),-1)) <=$(=AddYears(Date(Today()),-1))"}>}FactSoldAmount) --- for 2017

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),-2)) <=$(=AddYears(Date(Today()),-2))"}>}FactSoldAmount)  ----- for 2016

I want to have current month information as default but when I select the month it should show the selected month's data in these years.

I wrote this but it doesn't work

If( GetSelectedCount(Month)=0,

Sum(Sales),

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),0)) <=$(=AddYears(Date(Today()),0))"}>}Sales)

)   --------- for 2018

But it shows me the whole year's data.

7 Replies
YoussefBelloum
Esteemed Contributor

Re: Current Month , Current and previous years

EDITED:

Hi,

try this:

If( GetSelectedCount(Month)=0,

Sum(Sales),

Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

businessintelli
New Contributor III

Re: Current Month , Current and previous years

It shows me the whole year, but I want to show only the current month as default

Capture2.PNG

Also, When I  select month it shows me only one year, not all the 3 years .

YoussefBelloum
Esteemed Contributor

Re: Current Month , Current and previous years

Would you be able to attach some data (which represent your table structure) ?

businessintelli
New Contributor III

Re: Current Month , Current and previous years

Sorry, I can't 

the code that you suggested for 2018, works but incorrectly .

For example, today is April and I have data in January, March, February and April. If I choose May, it shouldn't show anything

Capture3.PNG

Also, other years doesn't work




mark6505
Valued Contributor III

Re: Current Month , Current and previous years

Hi,

I think that you need something like,

If( GetSelectedCount(Month)<>0,

Sum(Sales),

Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)


So you are saying if a month is selected the sum(Sales) Else ....


Mark

businessintelli
New Contributor III

Re: Current Month , Current and previous years

It doesn't work. For example, if I write only this

Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)


it shows current month information, but if I select month, for example May, What does it show?


Capture4.PNG


We don't have the information in May in 2018


mark6505
Valued Contributor III

Re: Current Month , Current and previous years

Hi,

When you remove the if i would expext to see what you are seeing.

The $ in your set analysis means inculde any selections, so you select May your date field is only looking at dates in May.

If you always want to show May, Then maybe change the $ to 1, but that would always ignore all selections.

I personally, make flags in my script to mark current month, Current year dates to make the Set analysis quicker and easier.

Mark

Community Browser