topic Help with Complex Expression in QlikView

Help with Complex Expression

Fri, 25 Nov 2016 10:56:01 GMT

Hi everyone,

I am struggling to create the following expression to be used in a chart.

I have a concatenated fact table which consists of Sales (record type 'SA') and First Sales Date (record type 'FS') which consists of a date when the customer had the first sale.

I want to create an expression where I get the total Sales for the month where the customers had the first Sales.

For ex for April 2016:
Total Sales in month $15,000
No of First Customers 230
Total Sales from first customers $8,500 (therefore Sales from returning customers $6,500)

I wrote the following expression:
=SUM({<AccountID=P({<Type={'FS'>}) >} Sales)

however the above will give me the sales of months 5,6,7, etc.. for the customers who came first active in Month 4, and not sales for just month 4.

Can anyone help me in this please?

I want to somehow achive the following condition
=SUM({<AccountID=P({<Type={'FS'}, Month_Number_Year={'4'}>} ), Month_Number_Year={'4'}>} Sales)

Many thanks for your help,

Jon

Message was edited by: Jonathan Mallia

Re: Help with Complex Expression

tresB — Fri, 25 Nov 2016 11:04:39 GMT

Do you get the output right with your last expression, i.e =SUM({<AccountID=P({<Type={'FS'}, Month_Number_Year={'4'}>} ), Month_Number_Year={'4'}>} Sales) ?

Could you post a sample qvw?

Re: Help with Complex Expression

Fri, 25 Nov 2016 11:06:32 GMT

Yes the last expression gives me the correct output because it returns the customers who had type FS in month 4, and Sales of month 4.

Thanks

Re: Help with Complex Expression

tresB — Fri, 25 Nov 2016 11:08:26 GMT

So you are probably trying to make it dynamic by setting a formula for '4'. Am I right? If so, please tell your logic in detail how you decide on '4' ? Perhaps a sample qvw with sample data ?

Re: Help with Complex Expression

Fri, 25 Nov 2016 11:10:37 GMT

What I need is something based on

=SUM({<AccountID=P({<Type={'FS'>}) >} Sales)

to be shown in a chart - a chart which shows the total sales from first time customers. The Dimension on this chart is simply Month.

Re: Help with Complex Expression

Fri, 25 Nov 2016 12:56:06 GMT

Hi Tresesco,

Did you manage to get hold of the sample app?

Thanks

Re: Help with Complex Expression

sunny_talwar — Fri, 25 Nov 2016 13:11:23 GMT

May be looking to get this?

Expression

=Sum(Aggr(If(AccountBrandID = Above(AccountBrandID) and Above(Type) = 'FS', Sum(Deposits)), Month_Number_Year, AccountBrandID, Type))

Re: Help with Complex Expression

Fri, 25 Nov 2016 14:02:37 GMT

Hi Sunny,

Thanks for the above. The Value for April should be 25,830 however. The April value in the chart should be the same as when selecting Apr from the top list.

Re: Help with Complex Expression

Fri, 25 Nov 2016 14:18:02 GMT

Hi everyone,

So after a while it looks like the below works...

Sum
(
Aggr( IF(COUNT(DISTINCT Type) = 2, SUM(Deposits),0), AccountBrandID, Month_Name_Abbreviation)
)

Anyone can confirm this and ensure it works in all scenarios?

Re: Help with Complex Expression

sunny_talwar — Fri, 25 Nov 2016 18:11:36 GMT

Both our expressions are giving the same values. Not sure what difference did you find?

1st expression is mind and second one is yours

Re: Help with Complex Expression

Fri, 25 Nov 2016 18:25:43 GMT

Thanks Sunny, Both provide the same result actually. Are the 2 expressions syntactically identical? You recon is there a better or cleaner way to write the expression? Many thanks again, Jon

Re: Help with Complex Expression

sunny_talwar — Fri, 25 Nov 2016 18:49:12 GMT

I think the two expressions are different ways to reach same result but can vary based on your actual data. In your case if both the expressions are working, I would suggest you to use your own expression as it is simpler and require a smaller aggregation.

HTH

Best,

Sunny

Re: Help with Complex Expression

jcmachado — Wed, 25 Jan 2023 20:45:26 GMT

You can use the following set expression to calculate the total sales for the month where customers had their first sale:

Sum({$<[First Sales Date.autoCalendar.Month]={"$(=Month([First Sales Date]))"}>} Sales)

This expression uses the "Sum" function to sum the sales for the selected month, specified by the First Sales Date field. The "First Sales Date.autoCalendar.Month" field is used to filter the data to the month of the first sale. The "Month" function is used to extract the month from the First Sales Date field.

To calculate the number of first customers, you can use the following expression:

CountD(Distinct {$<[First Sales Date.autoCalendar.Month]={"$(=Month([First Sales Date]))"}>} [Customer ID])

This expression uses the "CountD" function to count the number of distinct customer IDs in the selected month, specified by the First Sales Date field.

You can use these expressions in a chart, and then subtract the Total Sales from first customers from the Total Sales in the month to get the Sales from returning customers.