topic Re: Set analysis Syntax not working properly for current year and PY in QlikView

Set analysis Syntax not working properly for current year and PY

deepakqlikview_123 — Fri, 19 Aug 2016 04:37:30 GMT

Hi All,

PFA.

in text boxes I have shown it seems results are not showing properly for current year and Previous year.

We need to show the results for max year and max year - 1 for max month.

Please suggest.

Thanks

Re: Set analysis Syntax not working properly for current year and PY

Fri, 19 Aug 2016 05:20:06 GMT

Hi,

Syntax error is there

Correct syntax is

sum({<DOJ={$(=Max(Year)),$(=max(year-1))}>}sal)

Re: Set analysis Syntax not working properly for current year and PY

phaneendra_kunc — Fri, 19 Aug 2016 05:34:37 GMT

Looks ok to me. Where exactly you are not seeing the data or not able to match to the source.

Re: Set analysis Syntax not working properly for current year and PY

fashid — Fri, 19 Aug 2016 05:39:07 GMT

Loooks ok to me as you do not have data for 2018 it returns the correct result for 2017 .

Where do u find the problem .

Re: Set analysis Syntax not working properly for current year and PY

deepakqlikview_123 — Fri, 19 Aug 2016 05:42:30 GMT

Hi,

Thanks for the reply.

It seems it is not showing the results for maximum month number.

Regards,

Deepak

Re: Set analysis Syntax not working properly for current year and PY

stabben23 — Fri, 19 Aug 2016 05:43:21 GMT

Hi,

if you Select what you have in Your Expression, you have this:

Your Expression is also a little bit strange if you only what to see previous and current max(year).

Can you tell us more what you expect?

Re: Set analysis Syntax not working properly for current year and PY

fashid — Fri, 19 Aug 2016 05:53:28 GMT

The maximum month number for the the maximum year 2018 is April which does not have corresponding values in the Data field hence u are unable to see the values.

regards,

Nadeem

Re: Set analysis Syntax not working properly for current year and PY

deepakqlikview_123 — Fri, 19 Aug 2016 06:10:03 GMT

Hi,

For example

For 2017 max month is july so only data for july need to show.

For 2016 data only for Apr.

Thanks

Re: Set analysis Syntax not working properly for current year and PY

fashid — Fri, 19 Aug 2016 06:19:32 GMT

The in that case this is your corrected expression for current Year

=if((Sum({<Year={'$(=(max(Year)))'},Month_Num={"$(=max(Month_Num))"},Flag={'P&L_Actual'},PARTICULARS={'Net Revenue'}>}Data))

=0,

(Num(Sum({<Year={'$(=(max(Year)-1))'},Month_Num={"$(=max(Month_Num))"},Flag={'P&L_Actual'},PARTICULARS={'Net Revenue'}>}Data)*1000,'$ #,##0 K')),

(Num(Sum({<Year={'$(=(max(Year)))'},Month_Num={"$(=max(Month_Num))"},Flag={'P&L_Actual'},PARTICULARS={'Net Revenue'}>}Data)*1000,'$ #,##0 K'))).

check the screenshot

Regards,

Nadeem

Re: Set analysis Syntax not working properly for current year and PY

fashid — Fri, 19 Aug 2016 06:25:35 GMT

Re: Set analysis Syntax not working properly for current year and PY

stabben23 — Fri, 19 Aug 2016 06:37:57 GMT

Why use max(year) if you whant to show current year? Max year will give you max from you dataset, in Your case 2018. Why not build a variable that holds year(today()) and use variable if you what current year.

The if handling in Your Expression is not necessary then.

Re: Set analysis Syntax not working properly for current year and PY

fashid — Fri, 19 Aug 2016 06:42:37 GMT

Hi Staffan ,

I Also thought the same but if you look at his expression closely he is trying to check the summation of the Data field , i.e. if the Data feild is = 0 for the max(year) then calculate for the max(Year)-1 else the max year hence he needs the if expression in set analysis .

regards,

nadeem

Re: Set analysis Syntax not working properly for current year and PY

deepakqlikview_123 — Fri, 19 Aug 2016 07:03:17 GMT

Hi All,

Max year seems ok but if i need YTD june results how shall i go ahead based on month num.

Thanks

Re: Set analysis Syntax not working properly for current year and PY

stabben23 — Fri, 19 Aug 2016 07:25:09 GMT

=if((Sum({<Year={'$(=(max(Year)))'},Month_Num={"<=$(=max(Month_Num))"},Flag={'P&L_Actual'},PARTICULARS={'Net Revenue'}>}Data))
=0,
(Num(Sum({<Year={'$(=(max(Year)-1))'},Month_Num={"<=$(=max(Month_Num))"},Flag={'P&L_Actual'},PARTICULARS={'Net Revenue'}>}Data)*1000,'$ #,##0 K')),
(Num(Sum({<Year={'$(=(max(Year)))'},Month_Num={"<=$(=max(Month_Num))"},Flag={'P&L_Actual'},PARTICULARS={'Net Revenue'}>}Data)*1000,'$ #,##0 K')))

add <= before $ as in Expression above

Re: Set analysis Syntax not working properly for current year and PY

stabben23 — Fri, 19 Aug 2016 07:39:18 GMT

Yes, But current year is the year we are in now, if he want to handle "selected" year it is an other story. A textbox With current year header is for me a static value that shows current year no matter what selection. Oterwise I should show it in an other Object. Thats why I wondering about the if handeling.