topic Re: convert SUM expression to Set Analysis in QlikView

convert SUM expression to Set Analysis

Thu, 11 Jun 2015 22:02:27 GMT

Hi,

I am new to Qlikview and built a prototype for Expression calculation using formula SUM( if(CALC_DATE = vSelectedDate, QTY, 0))

vSelectedDate is a variable of the format 6/10/2015 12:00:00 AM

CALC_DATE is a date with values in the same format.

when I convert the expression to =sum( {<CALC_DATE={$(vSelectedDate)}>} QTY) I get zero.

Am I missing any date conversion in the statement?

How would I use the same logic but only compare the date portion without the time component?

sum(.{<date(CALC_DATE)={date($(vSelectedDate))}>} QTY) produces an expression error.

PS: I am using a Personal Edition version of Qlikview

Thanks much for your help!

Re: convert SUM expression to Set Analysis

Gysbert_Wassenaar — Fri, 12 Jun 2015 05:32:20 GMT

My guess is that vSelectedDate contains a text value and not a date. Possibly CALC_DATE also contains text values and not dates. See this blog post for some pointers: Why don’t my dates work? And this document: QlikView Date fields

Re: convert SUM expression to Set Analysis

nlmmaurya — Fri, 12 Jun 2015 06:50:44 GMT

Hi,

It seems to be date issue.First convert date in number format,use like this.

Floor(NUM(CALC_DATE)) AS CALC_DATE.

Re: convert SUM expression to Set Analysis

Wed, 17 Jun 2015 14:07:52 GMT

Thank you Gysbert and Neelam. I was able to get to expected result using

=SUM({<CALC_DATE={"=(date(CALC_DATE)=date($(vSelectedDate)))"} >} QTY)

The Set Analysis document by FABRICE44 on Page 14 notes not to use the dimension when using in the condition statement. Seems to work for me at least for now. I am not sure how I would create a integer key KeyAutoNumber as stated in that document.