Creating crosstab chart on Qlik Sense

[#1]

Hello all,

I am new on Qlik and I am stuck on a chart that I thought would be easy to create:

I need the rows on my cross table to be sum of fields (each row a sum of a Boolean field (0/1) of my data set) and the columns are subsets of the data set over where the sum needs to happen. I have a flat data set with several fields, but as an example of the few I need for this summary cross tab:

bo_admitflag  bo_enrolledflag    date_admit    enroll_year

1                       1                     12.01.2015     2016

.....

the rows and columns on the cross tables would be

Row                         201x EOY Admits                              201x EOY Enrolled                         201x YTD Admits                               

                                sum(bo_admitflag) for previous EOF  sum(enrolledflag) for previous EOY  Sum(bo_admitflag)  for YTD .

sum(bo_admitflag)

sum(bo_enrolledflag)

....

The EOY will be all prior to current year selected and for the YTD, the sum has to be up to the current day and the year in question; the last columns on this chart will be for the current year and current date.

See attached image of the kind of cross table I need.

The Qlik Sense desktop table chart allows me to create the aggregate functions but there are only columns on it; The pivot chart does not allow me to create aggregation expression as row only measures.

Thanks, Alice

mbaeyens‌digvijay‌@

[#2]

Dear Digvijay,

if I add admit_date on my filter and pick say 5/2/2016 to be used for the YTD columns, it filters the records for the EOY columns as well , and I dont want this effect. Can I implement a variable  where the EU enters a date or choose from a calendar? In which case I remove the admit_date filter and compare Enroll_Year with the yeartodate(variable_date) ? I am supposing that the Yeartodate function implies adding all the records tagged as for that Enroll_Year, but up to the date chosen.

I bought the book you suggested and I hope that is useful.

See the app attached and thank you again

[Digvijay_Singh]

Such things are managed through different options of set analysis.

Sum({1<Enroll_Year = {$(=Max(Enroll_Year)-1)},Admit_Date=>} bo_enrollflag)

Use the above one, it takes the complete data set (via '1' between {<) and Admit_Date=, will discard the selection in Admit_Date

[#4]

Cool, those are the examples I am looking for and not quite finding it. There is a real lingo behind it and I am having a hard time to process it.

Why the Current YTD Admits expression is not giving me any result? I am sure there is data there to add up until 5/2/2016 where 2016 is the Enroll Year I have selected:

=Pick(Match(Valuelist('Students','Gross Deposits','Withdrawn Deposits','Net Deposits','Yield'),

'Students','Gross Deposits','Withdrawn Deposits','Net Deposits','Yield'),

Sum({<Enroll_Year = {$(=Yeartodate(Admit_Date))}>} bo_admitflag),Sum({<Enroll_Year = {$(=Yeartodate(Admit_Date))}>} bo_depositflag),Sum({<Enroll_Year = {$(=Yeartodate(Admit_Date))}>} bo_depositwithdrawal),

Sum({<Enroll_Year = {$(=Yeartodate(Admit_Date))}>} bo_depositflag-bo_depositwithdrawal),Sum({<Enroll_Year = {$(=Yeartodate(Admit_Date))}>} (bo_admitflag/bo_applicant)))

Thanks again

[Digvijay_Singh]

You should use it like I suggested -

{Your date field={"=Yeartodate(Yourdate)=-1"}>}

The surrounding double quotes means its a search string, means all the dates will be selected where yeartodate results into true. Your current expression are trying to find records where enroll year equals to 0 or 1(result of yeartodate) and they will never match.

[#6]

Something like that:

Sum({<Enroll_Year = {"=Yeartodate(Admit_Date)=-1"}>} bo_depositflag)

what that -1 is doing?

It still return zero

[Digvijay_Singh]

Like this, for YTD dates, Yeartodate(Admit_Date) will return -1 means true.

Sum({<Admit_Date = {"=Yeartodate(Admit_Date)=-1"}>} bo_depositflag)

[Digvijay_Singh]

If you can just finish initial coding of all formulas and share the latest version, I will try to correct set expressions and send you back by tomorrow first half, or you can post new thread to get attention of other experts if you need it urgently.

[#9]

Thanks again,

See the attached app; it makes sense that it is Admit_Date, but I imagine that I have to limit it to the Enroll_year as well, no?

Also, how do I construct the expression for the Prior Year YTD? Do I have to parse Admit_Date to have prior year on it and also limit to the prior enroll year?

I know what to do, but I dont know the syntax...

The learning curve of Qlik is way higher than I thought.

[Digvijay_Singh]

Hi Alice,

I have corrected formula for YTD in the attached sample. But You need to understand from your business about where to use Enroll Date and where to use Admit_Date, When I see your data, you have situation where Enroll_Date is null but Admit Date is having valid value.

Also Yeartodate may not be used here as you want YTD based on any selection of the date.

I am sure now you can add the remaining formulas based on this sample.

Let me know if you get stuck any where.

Set Analysis looks complex initially, but you will find it very interesting once you get hands on it, I am still learning and there is a lot to learn to master it.

[#11]

thank you.

I can tell the set analysis is powerful and the heart of qlik; Is there any book only in set analysis?

I will review your example carefully. I had to shift gear to a spss analysis today.

A