topic Set Analysis Formula Question in QlikView

Set Analysis Formula Question

Anonymous — Tue, 15 Sep 2015 21:30:42 GMT

I'm placing the following expression in the text of a chart:

='Accrued Total Shipped MTD: ' & num(Sum({1<DATE=, [Act. PGI Date]={"$(=CMTD = 1"}, ShipmentStatus = {'COMPLETED'}>}DeliveredQty), '###,###,##0')

DATE is the field that's connecting the data model to the master accounting periods calendar in my application. I want to ignore it for the purposes of this expression. The ultimate intent is to have Act. PGI Date equal to or "search" the CMTD (Current Month To Date) field in my calendar where it equals '1' . No standard QlikView calendar functions will help for this, as it is a fiscal accounting periods calendar. When CMTD field is equal to '1', then the calendar knows to display data only associated with the fiscal period's beginning date to the current day.

I imagine i can also use vCurrent(fiscal)MonthStart and Today() to have the same effect as CMTD.

Thoughts?

Thanks,

Ron

Re: Set Analysis Formula Question

Gysbert_Wassenaar — Wed, 16 Sep 2015 06:10:41 GMT

[Act. PGI Date]={"$(=CMTD = 1"}

To begin with there's a syntax error in that bit. There's either a '(' too much or a closing ')' too few. Perhaps you should use CMTD={1} instead. But I can't be sure since I have no idea what your data model looks like. Perhaps what you want is not possible with your set analysis expression given the data in your document. Can you post a small qlikview document that illustrates the problem?

Re: Set Analysis Formula Question

sasiparupudi1 — Wed, 16 Sep 2015 06:46:50 GMT

Please post a sample application

Re: Set Analysis Formula Question

qlikviewwizard — Wed, 16 Sep 2015 07:12:34 GMT

Hi,

Your requirement is not clear. Can you explain in details?

Re: Set Analysis Formula Question

Anonymous — Thu, 17 Sep 2015 01:24:11 GMT

Sorry that I was a little unclear. The issue is that i can't get this expression to give any results:

='Accrued Total Shipped MTD: ' & num(Sum({1<[Act. PGI Date]={"$(=CMTD) = {1}"}, ShipmentStatus = {'COMPLETED'}>}DeliveredQty), '###,###,##0') //fixed the sytax (i think)

OR maybe this?

='Accrued Total Shipped MTD: ' & num(Sum({1<[Act. PGI Date]={">=$(=vCurrentMonthStart)<=Today()"}, ShipmentStatus = {'COMPLETED'}>}DeliveredQty), '###,###,##0')

Either should give the same result, but both come out to be 0:

I'm ultimately trying to get around the issue of my fiscal accounting calendar that contains the CYTD field (either 1 or 0 value, 1 being within the current month to date, 0 false) being run off of a certain date (DATE) that i'm using for the rest of the application. I dont want to complicate usability with a canonical date (DATE and Actual PGI Date concatenated on top of each other). Instead, i'd like to show a different result in the text box of the chart using the Actual PGI Date.

Actual PGI Date is in the LIPS (fact) table.

[Act. PGI Date]={">=$(=vCurrentMonthStart)<=Today()"} and [Act. PGI Date]={"$(=CMTD) = {1}"} should, in theory, have the exact same effect. I want to totally ignore the implications of DATE being connected to the master calendar and use Actual PGI Date for this one expression.

For example:

Sum({1<CMTD={'1'}>}DeliveredQty) works perfectly fine for that pie chart, but it's going off of DATE. I need to ignore DATE and use Actual PGI Date.

I can't post this application, sorry.

Thanks again,

Ron

Re: Set Analysis Formula Question

Gysbert_Wassenaar — Thu, 17 Sep 2015 05:03:11 GMT

Sum({1<CMTD={'1'}>}DeliveredQty) works perfectly fine for that pie chart, but it's going off of DATE. I need to ignore DATE and use Actual PGI Date.

Perhaps you should link your master calendar to Actual PGI Date then. Or simply create additional calender table based on Actual PGI Date.

[Act. PGI Date]={"$(=CMTD) = {1}"}

This is not a valid syntax. You could try [Act. PGI Date]={"=[Act. PGI Date]=[DATE]"},CMTD={1}. But that's unlikely to work (explanation here: set_analysis_intra-record.qvw)‌ and would require an if statement instead.