Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Would you be able to share you qvf file to look into this?
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.
Give this a read
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
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.
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
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;
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:
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)
I do not think you have dot (.) in your inwMonth column.
regards
Lech
Hi Lech,
I've not edited to the default variables script:
Datafield details below (again, as I'm very new to this, I did not do any treatment to the tags):
Thank you so much!