Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Need to get only one month data

Hi All,

I have a field called MonthYear starting from Jan 2014 to Sep 2017.

Now my problem is if i select any month in 2017 i need to get only Dec 2016 month data.

The same when i select any month in 2016 i need to get only Dec 2015 month data.


The same when i select any month in 2015 i need to get only Dec 2014 month data


Please let me know if you require anything more.


Attached app for more reference.


Thanks,

Bharat

1 Solution

Accepted Solutions
Master III
Master III

May be this

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)

I have changed Min to Max

View solution in original post

12 Replies
Master III
Master III

May be this

=If(Count({<MonthYear=P({1<Year={"$(=Year(Max(MonthYear)))"}>})>} DISTINCT MonthYear) = Count(DISTINCT MonthYear),
MonthStart(Min(MonthYear
),-1))

Creator III
Creator III

Hi Antonio,

Thanks for your reply in the above expression i need to calculate sum(IB) for the same condition. Can you please tell me where i have to include with the condition..

Thanks,

Bharat

Master III
Master III

In Document Property/Trigger

Set on Field, on Select Field

Field MonthYear

Expression

=If(Count({<MonthYear=P({1<Year={"$(=Year(Max(MonthYear)))"}>})>} DISTINCT MonthYear) = Count(DISTINCT MonthYear),Date(MonthStart(Min(MonthYear),-1),'MM/DD/YYYY'),
'('&Concat(DISTINCT MonthYear,'|')&')')

Creator III
Creator III

Thanks Antonio for your reply.

I will explain more in detail sorry if i explain wrong:

I have two fields IB and Month Year. Now in MonthYear i have data from Jan 2014 to Sep 2017. Now what ever year i select it should show sum(IB) only for december.

For example if i select Sep 2017 it should show Dec2016 Sum of IB.

The same if i select any month in 2016 it should show sum of IB for Dec 2015.

The expression what you have given is showing same month. Below image for reference:

T.PNG

Master III
Master III

Try this

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Min(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)

or in Select on Field

=Date(MonthStart(YearEnd(Min(MonthYear),-1)),'MM/DD/YYYY')

Creator III
Creator III

Thanks Antonio..

This expression is working perfectly fine:

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Min(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)


But only problem is, it is working only  if i select any date the MonthYear field.


Can this work even without selecting monthyear field..

Master III
Master III

In this case which would be the date to set?

Creator III
Creator III

We can assume that, it is this Year which will be Sep 2017...

Sorry if my explanation is wrong..

If the data refreshes for 2018 it should show 2017 dec sum(IB).

defautly if no date is selected then it will be Lastyear 2016 dec sum(IB)

In this scenario we have Sep 2017 data so default it should show 2016 dec sum(IB).. then if we select any month it should show last year december sum(IB).. Based on the selection.

If we don't select anything we should show by default last year 2016 dec sum(IB)..

Then if i make any year for e.g. if i select 2016 any month then it should show sum(IB) for december 2015..

If i remove all the selections then default last year 2016 dec sum(IB)


please let me know if you require anything more.

Master III
Master III

May be this

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)

I have changed Min to Max

View solution in original post