Could Set Analysis be applied to dimension?

[Anonymous]

Could Set Analysis be applied to dimension? If so, how to do it? Could you please give one example?

Or could Set Analysis be only applied to measure?

Thanks!

[Anonymous]

If(GetSelectedCount(myDate), Sum (myValue),  Sum({$ <  myDate *= {">=$(=Date(Yearstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"} >} myValue)    )

works perfectly.

Swuehl, thanks so much for your help! Great to learn from you, and hopefully this post will be also helpful to other Qlik members.Thanks again!

One last question about this topic. Finally, I need to load data from Oracle table. the format of myDate is 07-JAN-14 at Oracle table.In Qlik Data Load editor:

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';

My problem is that when I load myDate to Qlik, and then its format will become 2015-08-06 00:00:00.0000

I use:

Load *,

      date(myDate) as myDateFormat

From

     myJoinedTable

Order by myDate

And then I use myDateFormat as filter, however, when I use the following,

If(GetSelectedCount(myDateFormat), Sum (myValue),  Sum({$ <  myDateFormat*= {">=$(=Date(Yearstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"} >} myValue)    )

the date is not in order (seems random order)

I also try to create a dimension myDateDimension = date(myDate) and put my myDateDimension into above script, it does work.

I also try to order date(myDate) in Qlik load by referring the following, it does not work either.

Can you use 'Order By' in a QVD Load?

myTemp:

Load  *,

date(myDate) as myDateFormat

from myJoinedTable;

myNewData:

Load  *

Resident myTemp Order By myDateFormat;

Drop table myTemp;

(myNewData table does not load into Qlik)

Could you please give any suggestion? Thanks so much!

[swuehl]

For this:

myTemp:

Load  *,

date(myDate) as myDateFormat

from myJoinedTable;

myNewData:

Load  *

Resident myTemp Order By myDateFormat;

Drop table myTemp;

Here myNewData will be autoconcatenated to the myTemp table, you need to use:

myTemp:

Load  *,

date(myDate) as myDateFormat

from myJoinedTable;

myNewData:

NOCONCATENATE

Load  *

Resident myTemp Order By myDateFormat;

Drop table myTemp;

W.r.t your date issue,could you upload a sample application with just your date field loaded?

[Anonymous]

Swuehl, thanks so much for your help!

NOCONCATENATE works to load my new table (myNewData; myNewData will be autoconcatenated to the myTemp table; after we use  NOCONCATENATE , new table will not be dropped any more )

However, after I use [date(myDate) as myDateFormat; Resident myTemp Order By myDateFormat;], date is still not in order at Qlik. When I use date as dimension to create chart at Qlik, the date at dimension is still not in order (seems random). Any suggestion is appreciated!

I will be sure to update a sample file of my date problem.

Thanks!

[Anonymous]

Swuehl, thanks so much for your help!

I am still struggling about this problem, and I do not forget to update some sample file. I will be sure to do, and sorry for being late.

I use the following to plot a accumulative chart and could put the date in order now. (I label the x axis with start/end day of each week)

If(GetSelectedCount(myDate),

    RangeSum ( above ( Sum(myValue) , 0 , RowNo() ) ) ,

    RangeSum ( above ( Sum({< myYear *= {">=2016" } >} myValue) , 0 , RowNo() ) )

  )

I also uncheck "show null value" and "show zero value" at Qlik sense.

However, the problem is that the 0 zero is still at chart. I would like to only keep the non-zero value/plot on chart.

Could you please give any suggestion?