Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Combining multiple conditions on a measure

I have a measure which has to show data based specific accounts and sum it all up to the date selected in filter.

I have created this so far and I don't understand how to combine these two expressions in the same measure

1. Sum({$<account = {1,2,3,}>},amt)

2. Sum({$<year = {=max(year)}>},amt)

I tried creating variable for one of the conditions and still couldn't get this to work. Can someone please correct me.

1 Solution

Accepted Solutions

Re: Combining multiple conditions on a measure

Have you made a selection in date field? May be you need to restrict your expression to not change based on selection in date field:

Sum({$<account = {1,2,3}, year = {$(=max(year))}, date = , month = , monthyear = , Quarter = >} amt)

13 Replies

Re: Combining multiple conditions on a measure

Try this:

Sum({$<account = {1,2,3}, year = {$(=max(year))}>} amt)

Not applicable

Re: Combining multiple conditions on a measure

Unfortunately this din't work.

Sum({$<account = {1,2,3}>} amt)

this formula shows correct information but only for current date, but if I have to see the same amount for say dec 2013, I see only the records belonging to Dec 2013 and not all records summed till Dec 2013.

What should I do  to sum all information till the date selected by the filter?

Re: Combining multiple conditions on a measure

Have you made a selection in date field? May be you need to restrict your expression to not change based on selection in date field:

Sum({$<account = {1,2,3}, year = {$(=max(year))}, date = , month = , monthyear = , Quarter = >} amt)

Not applicable

Re: Combining multiple conditions on a measure

I tried this as well.
I completely removed the account selection and now I just want to convert the following query to qlik expression

           

         select SUM(amt) from t1 where date <= *date selection from the filter*

MVP
MVP

Re: Combining multiple conditions on a measure

Hi,

Try like this

Sum({$<year = , date ={'<=$(=Date(Max(Date)))'}, month = , monthyear = , Quarter = >} amt)


Hope this helps you.


Regards,

jagan.

Not applicable

Re: Combining multiple conditions on a measure

This still gives me the value for selected time frame and not the entire cumulative sum

Re: Combining multiple conditions on a measure

Is there any way you can share a sample with us Akshata?

Not applicable

Re: Combining multiple conditions on a measure

PeriodAmountCumulative_amt
201301-957005.99-957005.99
2013021798565.62841559.63
201303-787515.0854044.55
201304-23110.832530933.7175
201305617409.37648343.0875
2013061135189.381783532.468
2013071051420.772834953.238
201308966678.08713801631.325
201309-140738.14173660893.183
2013101001824.2194662717.402
2013111019397.2855682114.688
2013121036970.3516719085.039
2014011054543.4187773628.457
2014021072116.4848845744.94
2014031089689.559935434.49
201404-957005.998978428.5
2014051798565.6210776994.12
201406-787515.089989479.04
201407-23110.83259966368.207
201408-46636.294339919731.913
201409-70161.756179849570.157
201410-93687.2189755882.939
201411-117212.67989638670.259
201412-140738.14179497932.117

This is what my data looks like. I have created year_Name and Month_Name based on period column and use them to filter my data.

My end goal is to show 9755882.93 when the user selects Oct, 2014 and show 9497932.11 when there is no filter. but when I use the max condition, I am getting the data only for 2014 Jan to 2014 Oct

Re: Combining multiple conditions on a measure

This expression seems to work for the sample data provided:

=Num(Sum({<YearMonth = {"$(='<=' & Date(Max(YearMonth), 'YYYYMM'))"}, Year, Month>}Amount), '#,###.00')

No Selection

Capture.PNG

201410 Selected

Capture.PNG

Script:

Table:

LOAD Year(YearMonth) as Year,

  Month(YearMonth) as Month,

  *;

LOAD Date(Date#(Period, 'YYYYMM'), 'YYYYMM') as YearMonth,

    Amount,

    Cumulative_amt

FROM

[https://community.qlik.com/message/990029?et=watches.email.thread#990029]

(html, codepage is 1252, embedded labels, table is @1);

Community Browser