Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
Please see below My data,
Here i need for 2013 year hole sales and for 2014 year Jan to May data i need in my chart.
i wrote expression like this way but it's not working properly. =sum({$<Year = {$(=max(Year))}, Month =>} AOP_Cts/1000)
2013= here need Jan to Dec ...hole data
2014 = here i need Only Jan to May only
how we can write expression....
Year | Month | Sales |
2013 | 1 | 20.946 |
2013 | 2 | 21.061 |
2013 | 3 | 21.92 |
2013 | 4 | 21.848 |
2013 | 5 | 16.645 |
2013 | 6 | 23.912 |
2013 | 7 | 25.137 |
2013 | 8 | 24.963 |
2013 | 9 | 25.113 |
2013 | 10 | 23.429 |
2013 | 11 | 5.267 |
2013 | 12 | 21.411 |
2014 | 1 | 16.141 |
2014 | 2 | 15.855 |
2014 | 3 | 19.934 |
2014 | 4 | 21.189 |
2014 | 5 | 13.436 |
2014 | 6 | 20.667 |
2014 | 7 | 24.445 |
2014 | 8 | 24.596 |
2014 | 9 | 25.537 |
2014 | 10 | 22.119 |
2014 | 11 | 1.271 |
2014 | 12 | 15.13 |
Advance Thanks
Munna
The best thing is to create a new field in your script that concatenate year and month:
Load
...
year * 100 + month as yearmonth
...
resident
...
In this way you will have a field like 201307 ... 201401, 201402 ...
the expression become:
sum({$<yearmonth = {'<=201405'}>} AOP_Cts/1000)
obviously you can use a variable for the current month ...
Let me know
i would suggest to create yeamonth field in the back end and use it in set analysis.
Here u have condition like u should show all data before 2014 may,
can be done like
sum({<Yearmonth={'<201405'}>}sales)
u wil need to do some changes in back end like
num(Year&if(len(Month)=1,'0'&Month,Month)) as Yearmonth
U can even move 201405 to a vaiable for further modifications ion it.
-Sundar
Hi Sundarakumar,
As per ur expression we are getting only one (May)month data but i want in year 2014 jan+feb+mar+Apr+May... this five month data in this year, And Previous year 2013 i want hole data like Jan+feb+Mar....upto Dec..
Advance Thanks
Munna
Hi Munna,
Try like this
Script:
TableName:
LOAD
*,
Num(Year & Num(Month, '00')) AS YearMonth
FROM DataSource;
Now use this expression
sum({<Year=, Month=, Yearmonth={'<201405'}>} Sales)
Hope this helps you.
Regards,
Jagan.
sum({<Yearmonth={ < 201405}>}sales)
please note the less than symbol in set analysis it will look for yearmonths which is less than 201405 which means all the years and month before it. if u want a from date also u can very well add it as
sum({<Yearmonth={ < 201405>201301}>}sales)
we would suggest to move these numbers to variables to make it dynamic.
-Sundar
Hi Munna,
After creating Yearmonth column, you may try this :
sum({<Yearmonth={<=201405}>}sales)
Regards,
Chinna
Hi Jagan,
It's working fine but statically it shows correct when i select any one there's no any changes ..it is not a Dynamically .
Could you please check once more.
Regards
Munna