topic Re: Set Analysis in QlikView

Set Analysis

bharatkishore — Thu, 14 Sep 2017 14:16:28 GMT

Hi All,

I have one expression written in set analysis i.e.

=SUM({<Period_NR={'>=$(=vCurrYear_SelectedMnth1)'}>}IB_1)

This expression is working fine and i getting correct result.

I have written one more expression i.e.

=SUM({<Period_NR={'>=$(=vPrevYear_SelectedMnth1)'}>}IB_1)

This value is showing wrong is there anything i have written wrong.

In the variable vCurrYear_SelectedMnth1 i have written getfieldselections(Period_NR)

In the variable vPrevYear_SelectedMnth1 i have written AddMonths(getfieldselections(Period_NR),-12).

Please suggest

Thanks,

Bharat

Re: Set Analysis

mdmukramali — Thu, 14 Sep 2017 14:20:32 GMT

Hi,

Maybe

=SUM({<Period_NR={'>=$(=vPrevYear_SelectedMnth1)'},Period_NR=>}IB_1)

Re: Set Analysis

sunny_talwar — Thu, 14 Sep 2017 14:22:34 GMT

There could be multiple issues

1) Do you select just one Period_NR?

2) Do you make selection in any other date or time field other than Period_NR?

3) What is the format of Period_NR field?

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 14:22:49 GMT

Thanks for the quick reply Mukram, but the expression what you have given i m getting wrong..

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 14:28:34 GMT

Hi Sunny,

Yes there will always one selected.

I don't make any other selection in any other date or time field.

format is given below image

Please let me know if you require anything more..

Re: Set Analysis

sunny_talwar — Thu, 14 Sep 2017 14:30:51 GMT

Try these

=Sum({<Period_NR = {">=$(=Date(AddMonths(Max(Period_NR), -12), 'MM/DD/YYYY'))"}>} IB_1)

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 14:39:26 GMT

Hi Sunny ,

I have tried your expression but the value is wrong:

Please see the below images :

When i select the current month i m getting correct value

The same when i select last year i.e. 07/01/2016 instead of 4392 i m getting 83908

Re: Set Analysis

sunny_talwar — Thu, 14 Sep 2017 14:47:02 GMT

Is it the same text box? If it is then when you selected 07/01/2016, it is showing the value from 07/01/2015

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 14:51:03 GMT

Hi Sunny,

Please find the attached app which will be helpful.

Thanks,

Bharat

Re: Set Analysis

sunny_talwar — Thu, 14 Sep 2017 14:54:57 GMT

Try this

=Sum({<Period_NR = {"$(=Date(AddMonths(Max(Period_NR), -12), 'MM/DD/YYYY'))"}>} IB_1)

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 15:00:47 GMT

Thanks Sunny,

But when i try to pass it in a variable then after writing set analysis statement it is showing wrong value.

Please see the attached doc..

Re: Set Analysis

sunny_talwar — Thu, 14 Sep 2017 15:04:02 GMT

You seem to need a single day, yet you use >=... can you elaborate why? Seems to work without it

=SUM({<Period_NR={'$(=vPrevYear_SelectedMnth1)'}>}IB_1)

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 15:14:39 GMT

Thanks a lot sunny.. But i have two queries can you please help me whose:

1) If i use only

=AddMonths(GetFieldSelections(Period_NR),-12) it is not working.

But when i use =Date(AddMonths(Max(Period_NR), -12), 'MM/DD/YYYY') it is working.

2) For testing i have written >=, can't i use this one what will happen if i don't use >= request you to suggest me these two.

Re: Set Analysis

sunny_talwar — Thu, 14 Sep 2017 15:30:09 GMT

1) Working for me with this

=Date(AddMonths(GetFieldSelections(Period_NR), -12), 'MM/DD/YYYY')

May be an issue with the date format

2) = is for the day and >= is for all day after the selected day (including the selected day)... what do you want?

Re: Set Analysis

bharatkishore — Thu, 14 Sep 2017 15:34:09 GMT

thanks a lot sir... Thank you so much