topic Set Analysis troubleshooting in QlikView

Set Analysis troubleshooting

sarafamiglietti — Wed, 31 Jan 2018 13:24:18 GMT

Hello,

I want to calculate sales when date equals max date of the selected period.

Can someone helps me?

For example :

For week 2017/44, I want to display sum of the max date of this week, so the sales of the 05/11/2017 (and not the sales of the complete week).

Thanks and regards

Re: Set Analysis troubleshooting

sunny_talwar — Wed, 31 Jan 2018 13:26:32 GMT

Try this

Sum({$ <dt_jour={"$(=Date(max(dt_jour)))"}>} nb_transaction)

Re: Set Analysis troubleshooting

sunny_talwar — Wed, 31 Jan 2018 13:27:43 GMT

Or this

FirstSortedValue(Aggr(Sum(nb_transaction), dt_jour), -dt_jour)

Re: Set Analysis troubleshooting

sarafamiglietti — Wed, 31 Jan 2018 13:33:05 GMT

Hello,

Formula with FirstSortedValue is working!

Tanks a lot!

Re: Set Analysis troubleshooting

sarafamiglietti — Tue, 06 Feb 2018 15:08:59 GMT

Hello,

I bounce on my previous question : what would be the formula if I add a cyclic group as dimension?

Thanks and regards

Sara

Re: Set Analysis troubleshooting

sunny_talwar — Tue, 06 Feb 2018 15:10:00 GMT

Would need to see what dimensions you have and what is the output you expect

Re: Set Analysis troubleshooting

pradosh_thakur — Tue, 06 Feb 2018 15:44:21 GMT

try

getcurrentfield('Cyclicgroupname') if you add cyclic group as dimesnion

Re: Set Analysis troubleshooting

sarafamiglietti — Wed, 07 Feb 2018 10:02:38 GMT

Hello,

I had an example on my previous answer.

There are 2 formulas: when I use dimensions in a cyclic group and when I don't use a cyclic group.

Don't hesitate if you need more precision.

Thanks & Regards

Re: Set Analysis troubleshooting

sunny_talwar — Wed, 07 Feb 2018 12:13:22 GMT

May be this

Sum({$<dt_jour = {"$(=max(dt_jour))"}>} nb_transaction)

Re: Set Analysis troubleshooting

sarafamiglietti — Wed, 07 Feb 2018 16:02:53 GMT

It works fine! Thanks a lot.

Just to understand :

Why my formula did not work? -> Sum({$ <dt_jour={'=max(dt_jour)'}>} nb_transaction)

In the Set analysis, what is the difference between {'=max(dt_jour)'} and {"$(=max(dt_jour))"}?

Thanks and regards

Re: Set Analysis troubleshooting

sunny_talwar — Wed, 07 Feb 2018 16:10:12 GMT

Yours would have worked as a search string (in the past, have been fixed now in the newer version to only use double quotes) Quotes in Set Analysis. Where as dollar sign expansion gives a single value against which the chart is created. This in your case is the max(dt_jour). The Magic of Dollar Expansions

Re: Set Analysis troubleshooting

sarafamiglietti — Tue, 20 Feb 2018 16:26:33 GMT

Hello,

Sorry to bother you again but it seems that the previous formula don't work in any case.

For example if I select several period like week 44 and 45 and if I select year_week in my cyclic group, data for week 44 are equal to 0.

Tell me if you need more details.

Regards

Sara

Re: Set Analysis troubleshooting

sunny_talwar — Tue, 20 Feb 2018 16:40:12 GMT

Please find attached

Re: Set Analysis troubleshooting

sarafamiglietti — Wed, 21 Feb 2018 09:13:08 GMT

Great!

Thanks a lot!

Regards

Sara