topic Re: Need to get only one month data in QlikView

Need to get only one month data

bharatkishore — Fri, 27 Oct 2017 07:24:56 GMT

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

Re: Need to get only one month data

antoniotiman — Fri, 27 Oct 2017 07:57:49 GMT

May be this

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

Re: Need to get only one month data

bharatkishore — Fri, 27 Oct 2017 08:29:45 GMT

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

Re: Need to get only one month data

antoniotiman — Fri, 27 Oct 2017 09:15:51 GMT

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,'|')&')')

Re: Need to get only one month data

bharatkishore — Fri, 27 Oct 2017 09:28:39 GMT

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:

Re: Need to get only one month data

antoniotiman — Fri, 27 Oct 2017 10:10:06 GMT

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')

Re: Need to get only one month data

bharatkishore — Fri, 27 Oct 2017 10:31:18 GMT

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..

Re: Need to get only one month data

antoniotiman — Fri, 27 Oct 2017 10:34:26 GMT

In this case which would be the date to set?

Re: Need to get only one month data

bharatkishore — Fri, 27 Oct 2017 10:42:22 GMT

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.

Re: Need to get only one month data

antoniotiman — Fri, 27 Oct 2017 10:53:45 GMT

May be this

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

I have changed Min to Max

Re: Need to get only one month data

bharatkishore — Fri, 27 Oct 2017 10:56:09 GMT

Thanks a lot... Exactly what i needed thank you so much.

Can you tell me what is difference between Min and Max for the expression...

Re: Need to get only one month data

antoniotiman — Fri, 27 Oct 2017 14:04:04 GMT

Max() returns Max MonthYear (sep 2017) when no date is selected.

Re: Need to get only one month data

bharatkishore — Mon, 30 Oct 2017 06:55:00 GMT

Thank you....