topic Re: Set Analysis not working for me..! in QlikView

Set Analysis not working for me..!

sagar_vij99 — Wed, 25 Nov 2020 16:16:04 GMT

I have a table with some fields with sample data and i have created field using "MonthName(MakeDate(P_Yr, Month(Date#(P_Month,'MMMM'))))" as Month_Name in script.

P_Month P_Yr Time Month_Name

January 2018 40 January 2018

January 2018 30 January 2018

January 2018 40 January 2018

December 2017 10 December 2017

December 2017 40 December 2017

November 2017 20 November 2017

November 2017 60 November 2017

You can consider this as the sample data bcz i have huge data but somewhat similar.

Now the problem is i am calculating sum of time for current selected month and for previous month. For previous month i am using below set expression :

sum ({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>}Time)

When i am selecting only months from list box everything is working fine but as soon as i select 2018 from list box(P_Yr) it shows me data for January 2018 but it does not show for December 2017.

I tried to use Month_Name in set and tried below expression but it always shoes me 0 as value:

sum({$<Month_Name={"$(=MonthName(Month_Name-1))"}>}Time)

Can anyone please see what incorrect i am doing here. Any help will be appreciated..!!

Regards

Sagar

Re: Set Analysis not working for me..!

Anonymous — Thu, 15 Mar 2018 05:27:55 GMT

Hi,

In your set analysis, I think you should also consider the year.

Perhaps: sum ({$<P_Yr = {$(=Max(P_Yr ))}, P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>}Time) and

sum({$<P_Yr = {$(=Max(P_Yr ))}, Month_Name={"$(=MonthName(Month_Name-1))"}>}Time)

Re: Set Analysis not working for me..!

PrashantSangle — Thu, 15 Mar 2018 05:59:23 GMT

Yes it is executing correctly. If you want nullify selection of year in your current expression then you have to modify it as

sum ({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"},P_Yr =>}Time)

Regards,

Re: Set Analysis not working for me..!

jyothish8807 — Thu, 15 Mar 2018 06:06:27 GMT

Try this:

sum ({$<Month_Name={"$(=monthname(Addmonths(Month_Name,-1)))"},P_Month=,P_Yr=>}Time)

Br,

Re: Set Analysis not working for me..!

sagar_vij99 — Thu, 15 Mar 2018 06:42:21 GMT

Hi Prashant,

I tried this solution but it will not work when i have select January 2017. It shows sum of all December Data data whereas it should show December 2016 data only.

So i changed my Months data and brought it to numbers instead of Text. Now my expression is:

=sum({$<P_Month={"$(=Num(Month(Month_Name-1)))"},P_Yr={"$(=Num(Year(Month_Name-1)))"}>}Time)

Still the problem remains same as i select 2018 it shows me 0 for previous month. I mean when year is changing it is creating issue else it is working fine when i am selecting months from same year.

Re: Set Analysis not working for me..!

sagar_vij99 — Thu, 15 Mar 2018 06:42:41 GMT

Hi,

I tried this solution but it will not work when i have select January 2017. It shows sum of all December Data data only whereas it should show December 2016 data only.

So i changed my Months data and brought it to numbers instead of Text. Now my expression is:

=sum({$<P_Month={"$(=Num(Month(Month_Name-1)))"},P_Yr={"$(=Num(Year(Month_Name-1)))"}>}Time)

Still the problem remains same as i select 2018 it shows me 0 for previous month. I mean when year is changing it is creating issue else it is working fine when i am selecting months from same year.

Re: Set Analysis not working for me..!

jyothish8807 — Thu, 15 Mar 2018 06:44:56 GMT

Give a try to my solution. Keep the expressions as simple you can

Re: Set Analysis not working for me..!

sagar_vij99 — Thu, 15 Mar 2018 06:51:17 GMT

It is giving me 0 for all selections.

Re: Set Analysis not working for me..!

jyothish8807 — Thu, 15 Mar 2018 06:58:02 GMT

Check the sample:

Re: Set Analysis not working for me..!

sagar_vij99 — Thu, 15 Mar 2018 06:59:49 GMT

I guess you missed the sample..

Re: Set Analysis not working for me..!

sasiparupudi1 — Thu, 15 Mar 2018 07:39:03 GMT

Expression

=sum({<Month_Name={"$(=Date(addmonths(Month_Name,-1),'MMMM-YYYY'))"}>}Time)

In the script

Load

Date(MakeDate(P_Yr, Month(Date#(P_Month,'MMMM'))),'MMMM-YYYY') as Month_Name,

* Inline

[

P_Month,P_Yr,Time,Month_Name1

January,2018, 40, January 2018

January,2018, 30, January 2018

January,2018, 40, January 2018

December,2017, 10, December 2017

December,2017, 40, December 2017

November,2017, 20, November 2017

November,2017, 60, November 2017

];

Re: Set Analysis not working for me..!

sagar_vij99 — Thu, 15 Mar 2018 08:23:38 GMT

Hi Sasidhar,

Thanks for the help.. It is working fine but my only concern is when i make any selection from filter P_Month, for previous month numbers appear as 0.

And in my dashboard P_Yr and P_Month are filter(used as list box).

Can you explain why it is happening and how we can resolve this.?

Re: Set Analysis not working for me..!

jyothish8807 — Thu, 15 Mar 2018 08:42:33 GMT

i can see the sample

Re: Set Analysis not working for me..!

qliksus — Thu, 15 Mar 2018 08:59:26 GMT

Have you checked whether previous month variable returning the correct month value ? You can add the previuous month expression in a chart with label and see what value is passed in SET analysis . Can you try creating a yearmonth field and try the below expression ?

sum({<Yearmonth={$(=date(addmonths(max(P_year)&max(P_Month),-1),'YYYYMM'))}>}time)

Re: Set Analysis not working for me..!

anagharao — Thu, 15 Mar 2018 09:24:01 GMT

try

=SUM({<P_Month=, P_Yr=, Month_Name={"$(=DATE(AddMonths(DATE#(Month_Name,'MMMM YYYY'),-1),'MMMM YYYY'))"}>}Time)

Re: Set Analysis not working for me..!

sasiparupudi1 — Thu, 15 Mar 2018 12:31:09 GMT

May be try

=sum({1<Month_Name={"$(=Date(addmonths(Month_Name,-1),'MMMM-YYYY'))"}>}Time)