Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
erictanzh
Contributor III
Contributor III

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

17 Replies
sunny_talwar

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

erictanzh
Contributor III
Contributor III
Author

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
Champion III
Champion III

Give this a read

Why don’t my dates work?

Quotes in Set Analysis

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable

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
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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". Please LIKE threads if the provided solution is helpful to the problem.
erictanzh
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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". Please LIKE threads if the provided solution is helpful to the problem.
erictanzh
Contributor III
Contributor III
Author

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!