Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

erictanzh
New Contributor

Set Analysis Returning Incorrect Values

Hi everyone,

I am very new to the coding, and I have a weird phenomenon affecting my set analysis results.


Essentially, I'm looking to only sum field values for only "Sep" dates. It performs its job to force all other months' values to zero but I'm seeing incorrect and even zero values being returned for selected September dates, resulting in incorrect summaries. Ideally, the rightmost column should correspond to sum(inwVolume).


Would a kind and enlightened please advise on my error, many thanks in advance.


Set Analysis Error.JPG

Tags (1)
17 Replies

Re: Set Analysis Returning Incorrect Values

Would you be able to share you qvf file to look into this?

erictanzh
New Contributor

Re: Set Analysis Returning Incorrect Values

Hi Sunny,

Unfortunately, I'm only operating the browser version and hence am unable to export a QVF file.

But based on the screenshots, is there any other approach I try out with my expressions?

Thank you in advance.

vinieme12
Esteemed Contributor II

Re: Set Analysis Returning Incorrect Values

sumitkulkarni
New Contributor III

Re: Set Analysis Returning Incorrect Values

Hi Eric,

If you are looking to only sum field values for "Sep" dates then you can also try below formula with 'IF' statement.

I did try both ways with set analysis and If statement, which works for me without any problem.

However, you can try with 'IF' statement and let me know the result.


Sum(If(inwMonth='Sep.',inwVolume))

Thank you!

Best Regards,

Sumit Kulkarni

erictanzh
New Contributor

Re: Set Analysis Returning Incorrect Values

Hi Sumit,

Thank you,

I've tried your recommendation by incorporating "IF' and encapsulating with double quotes, yet it still doesn't return the correct values.

Set Analysis Error2.JPG

Lech_Miszkiewicz
Honored Contributor III

Re: Set Analysis Returning Incorrect Values

Hi Eric,

Although If statement can work, it is a better practice to use SetAnalysis for performacje reasons (IF statement = performance killer)

To solve your problem it would be great to see your inwMonth column values. Also you use " " characters in your search string for Set Analysis, where i would use single quotes '  '. The other thing i see there is that you have Sep. with dot (.) after Sep value. Is this what you have in inwMonth column?

Another thing you need to consider is your data model. How do you create your inwMonth column. Is this field created by autocalendar or you have created it by yourself? Is it text, integer or dual value.

regards

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
erictanzh
New Contributor

Re: Set Analysis Returning Incorrect Values

Thanks Lech,

I do indeed notice that the data tables take a much longer time to generate the results when I tried using the "IF" statement.

I'm also using double quotes based on good habit advocated in this article Quotes in Set Analysis. I did have a go using the single quote, but it yielded the same incorrect outcome as with using double quotes.

On the source inwDate values, this data is derived from a company systema and the values are stored in an excel xlsx format using the custom date formatting by Excel. And when I upload the data onto Qlik Sense, I actually passed the values through a calendar script (copied from a resource found in Qlik forum) and yes it seems that the MMM-YYYY format contains a "." after MMM.

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(inwDate) as minDate, 

               max(inwDate) as maxDate 

Resident Inwards; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

TempinwCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MstinwCalendar:

Load 

               TempDate AS inwDate, 

               week(TempDate) As inwWeek, 

               Year(TempDate) As inwYear, 

               Month(TempDate) As inwMonth, 

               YeartoDate(TempDate)*-1 as inwCurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as inwLastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as inwRC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as inwMonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as inwQuarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as inwWeekYear

Resident TempinwCalendar 

Order By TempDate ASC; 

Drop Table TempinwCalendar;

Lech_Miszkiewicz
Honored Contributor III

Re: Set Analysis Returning Incorrect Values

Hi

I think you are not using the right value for Set filter.

Can you please screenshot your QlikSense variables so we can see your default month names:

1.png

can you also screenshot preview of your table viewer with inwMonth selected so we can see datatype and its previewed values (like on my screenshot below)

2.png

I do not think you have dot (.) in your inwMonth column.

regards

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
erictanzh
New Contributor

Re: Set Analysis Returning Incorrect Values

Hi Lech,

I've not edited to the default variables script:

Qlik Variables.JPG

Datafield details below (again, as I'm very new to this, I did not do any treatment to the tags):

inwDate Data Details.JPG

Thank you so much!

Community Browser